CODEWITHSUNDEEP
sqlsql-servert-sql
dazzathedrummer
dazzathedrummer

Reputation: 531

SQL wildcards - trying to find a sequence of chars/nums within a string

I'm trying to find a specific pattern within a string. The pattern is six characters (can be alpha or numeric) followed by '/' followed by three numerals.

ABCDEF/123

the following is an example of a string...

HMLR/MUR130/001

I'm trying to extract the rightmost 10 characters (they don't always appear at the end, so RIGHT() isn't and option).

I'm trying to get my head around SQL wildcards, this is what I've got so far...

 CHARINDEX('______/[0-9]___',Nom_details,1)

...but I'm not getting any results back...what am I doing wrong?

Upvotes: 3

Views: 1231

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

To get the index of the match, you can use PATINDEX:

SELECT PATINDEX('%______/[0-9][0-9][0-9]%', 'HMLR/MUR130/001')

The function CHARINDEX doesn't support patterns.

To extract the match from data you can use SUBSTRING with the above PATINDEX:

WITH test (data) AS (  
    SELECT 'HMLR/MUR130/001'
    UNION ALL
    SELECT 'ABCDEF/123'
    UNION ALL
    SELECT 'someotherstuff'
)
SELECT SUBSTRING(data, PATINDEX('%______/[0-9][0-9][0-9]%', data), 10) 
FROM test
WHERE data LIKE '%______/[0-9][0-9][0-9]%'

demo on dbfiddle.uk

Upvotes: 6

Related Questions