CODEWITHSUNDEEP
sql-server
Shine
Shine

Reputation: 197

How can i find the pattern identified by PATINDEX()

Which pattern is identified by PATINDEX in the below statement? Could any one help me analyse it? How can we find which of ('I','II','III') is identified ?

select PATINDEX ('%[I,II,III]%','sjfhasjdg II')

Please help me finding it.

Upvotes: 1

Views: 112

Answers (1)

Martin Smith
Martin Smith

Reputation: 453817

This is not how you use PATINDEX. , is not an alternation operator.

You are telling it to find characters in the set I,II,III which just repeats a lot of characters so can be simplified to "find the first location of either I or ,"

You could try

WITH SearchTerms(Term)
     AS (SELECT 'I'
         UNION ALL
         SELECT 'II'
         UNION ALL
         SELECT 'III'),
     ToBeSearched(string)
     AS (SELECT 'sjfhasjdg II')
SELECT string,
       Term,
       Charindex(Term, string) AS Location
FROM   ToBeSearched
       JOIN SearchTerms
         ON Charindex(Term, string) > 0

Returns

string       Term Location
------------ ---- -----------
sjfhasjdg II I    11
sjfhasjdg II II   11

Of course both I and II match as anything that matches the second will always match the first.

Upvotes: 1

Related Questions