CODEWITHSUNDEEP
sql-server
Shiva
Shiva

Reputation: 1399

Function to find the Exact match in Microsoft SQL Server

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.

Upvotes: 1

Views: 9347

Answers (3)

Sean Bright
Sean Bright

Reputation: 120644

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.

Upvotes: 1

Matt Rogish
Matt Rogish

Reputation: 24873

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)

Upvotes: 0

Mark
Mark

Reputation: 6301

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.

Upvotes: 0

Related Questions