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')

Answer 1

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

Answer 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.

Answer 3

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) )