Function to find the Exact match in Microsoft SQL Server

Question

What is the way to find the exactly matching substring in the given string in Microsoft SQL server?

For example, in the string '0000020354', I want to find '20354'. Of course it has to be an exact match. I tried to use CHARINDEX(@providerId, external_prv_id) > -1, but the problem with CHARINDEX is that it gives me the index as soon as it finds the first match.

Basically I am looking for function like indexOf("") in Microsoft SQL SERVER.

Do you mean find "2035" (not "2034") in "0000020354"? You might want to edit the question. — MattH, Apr 10 '09 at 20:08

score 1 · Answer 1 · edited Jan 08 '13 at 11:26

1

Assuming @ProviderId is a VARCHAR

You could just use LIKE :

SELECT Id FROM TableName WHERE Column LIKE '%' + @ProviderId + '%'

Which will return rows where Column contains 2034.

And if you don't want to use LIKE, You can use PATINDEX:

SELECT Id FROM TableName WHERE PATINDEX('%' + @ProviderId + '%', Column) > 0

Which returns the starting position of any match that it finds.

edited Jan 08 '13 at 11:26

Paul C

4,687
5
39
55

answered Apr 10 '09 at 19:58

Sean Bright

118,630
17
138
146

this value is always dyanmic. I dont know that value ahead of time – Shiva Apr 10 '09 at 20:02
that number is just an example – Shiva Apr 10 '09 at 20:03
Your question is confusing, but I reckon PATINDEX is what you want. There's no problem with paramaterising the 2034 Sean's written. Plug it in where you used CHARINDEX above. – Mark Apr 10 '09 at 20:51

Mark · Answer 2 · 2009-04-10T21:17:11.013

What's the data you're storing? It sounds like another storage type (e.g. a separate table) might be more suitable.

Ahh, 2034 was a typo. What I don't understand from your question is that you say you need the exact match. If CHARINDEX returns non-zero for '20354' you know that it's matched '20354'. If you don't know what @providerId is, return that in your query along with the result of CHARINDEX. Similarly, if you want external_prv_id, include that, e.g.:

SELECT external_prv_id, CHARINDEX(@providerId, external_prv_id)
       WHERE CHARINDEX(@providerId, external_prv_id) > 0

(Note that CHARINDEX returning 0 means it was not found.)

If you actually mean that '20354' could include wildcards, you need PATINDEX.

score 0 · Answer 3 · edited May 23 '17 at 12:01

The LIKE %VAL% stuff will be overly broad, e.g. the database contains 00000012345 and you search for 1234 you'll pull this row, which is what the OP does not intend (if I'm understanding the "EXACT" part correctly).

What you want is a regular expression that does something like: any number of zeroes followed by the match and end of line.

From this question we know how to trim leading zeroes: Better techniques for trimming leading zeros in SQL Server?

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

So, combine that with your query, and you can do something like the following:

WHERE SUBSTRING(external_prv_id, PATINDEX('%[^0]%', external_prv_id+'.'), LEN(external_prv_id)) = '12345'

Of course, the better (best?) solution would be to store them as INTEGERS so you get full indexability and don't have to muck with all of this crap. If you REALLY need to store the exact string then you have a couple of options:

store the normalized integer results in another column and use that for all internal queries
always store an integer but then pad with zeros upon query (my vote)

Function to find the Exact match in Microsoft SQL Server

3 Answers3