Usage on Patindex() function
I am using patindex
fo开发者_运维问答r 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')
select T.Value
from (values
(charindex('MD', 'FHoisegh MD')),
(charindex('DO', 'FHoisegh MD'))
) as T(Value)
where T.Value > 0
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.
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) )
精彩评论