Usage on Patindex() function

Question

I am using patindex for pattern search of "MD" or "DO". The below statement returns 3. Am I using it in the wrong way or is there another way of checking condition?

select PATINDEX ('%[MD,DO]%','FHoisegh MD')

`PATINDEX` doesn't have any kind of alternation operator you would need to use CLR and Regex for this or two expressions — Martin Smith, Sep 15 '11 at 08:58
@Ratz My answer does not work, as indicated in the comments. If you could unaccept my answer, I can delete it. — Jeff Ogata, Sep 16 '11 at 16:59

score 3 · Answer 1 · answered Sep 16 '11 at 05:37

3

select T.Value
from (values
        (charindex('MD', 'FHoisegh MD')),
        (charindex('DO', 'FHoisegh MD'))
     ) as T(Value)
where T.Value > 0

answered Sep 16 '11 at 05:37

Mikael Eriksson

136,425
22
210
281

Jeff Ogata · Accepted Answer · 2011-09-15T16:10:54.807

2

select PATINDEX ('%[MD][DO]%','FHoisegh MD')  -- returns 10

As you have it in your question, it is looking for any of the 5 characters between the square brackets, and finds 'o' at position 3. For example,

select PATINDEX ('%[MD,DO]%','F,Hoisegh MD')  -- returns 2

As @Filip De Vos pointed out, '%[MD][DO]%' will also match MO. The only way I can think of to handle this would be to subtract out the index for MO:

select PATINDEX ('%[MD][DO]%','FHoisegh MO') - PATINDEX('%MO%', 'FHoisegh MO') -- returns 0

If MD and DO are the only terms you'll be searching for, this might suffice. For anything else, I'd say look for other ways to do it.

edited Sep 15 '11 at 16:10

answered Sep 15 '11 at 07:29

Jeff Ogata

56,645
19
114
127

So in your solution it will find `MO` also. – Filip De Vos Sep 15 '11 at 07:55
What numeric result in your new version indicates success? `ALMODOVAR` contains `DO` but your new version returns `0` – Martin Smith Sep 15 '11 at 16:17

Bogdan Sahlean · Answer 3 · 2011-09-16T09:17:18.130

With a combination of PATINDEX/CHARINDEX, NULLIF & ISNULL functions you can get the desired result:

DECLARE @text VARCHAR(100) = 'FHoisegh MXD';
SELECT ISNULL( NULLIF(PATINDEX('%MD%',@text),0), PATINDEX('%DO%',@text) )

If you search for three or more values (ex. DO / RE / MI) instead of ISNULL function can be used COALESCE function:

DECLARE @text VARCHAR(100) = 'abcMODOKO';
SELECT COALESCE( NULLIF(PATINDEX('%DO%',@text),0), NULLIF(PATINDEX('%RE%',@text),0), PATINDEX('%MI%',@text) )

Usage on Patindex() function

3 Answers3

Linked