Is it possible to use LIKE in a SQL query to look for patterns of numbers and letters. I need to locate all records where the specific field data has the pattern (2 Numbers,1 hyphen, 3 Letters) ## - AAA I am using SSMS with SQL Server 2008. Any help would be appreciated. THANKS.
views:
94answers:
8
+2
A:
I would recommend creating a CLR assembly with .Net. That way you can create a function or SP that can use regex.
Edit: Even though this can be done via LIKE as stated in other answers, I would still recommend creating the assembly. If your data changes for some reason and you need an advanced way of looking up data, this regex assembly would be able to accomodate the change
Chris Klepeis
2010-06-16 15:35:37
Using CLR regexes is pretty much overkill for this. Also, using a `LIKE` when possible will usually be much faster, because it's native to SQL Server and it can use indexes better (if you can prevent an initial '%' or '_').
Ruben
2010-06-16 15:44:22
Interesting. I will look into this for future use. Thanks
2010-06-16 19:02:32
If people mark me down, I really wish they'd leave a comment explaining why.... otherwise how am I ever supposed to learn what's wrong with the answer I've posted?
Mark Baker
2010-06-16 15:43:47
I didn't downvote you, but if you read my answer, I hope this sheds some light on the downvote.
Ruben
2010-06-16 15:50:55
I did not mark you down, but if I had to guess... I would say you were probably marked down because you stated this could not be done with like, which is wrong.
G Mastros
2010-06-16 15:53:17
@Mark I didn't downvote you either but would guess that it is because your answer implies it is not possible using like when it actually is.
Martin Smith
2010-06-16 15:53:49
Thaks for the responses... I don't object to being downvoted when I've posted something incorrect, just to not being advised why I'm incorrect.... thanks to all for explaining
Mark Baker
2010-06-16 16:05:03
+6
A:
I think LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%' should work.
I'm not sure of your case sensitivity requirements but you can stick a COLLATE in as below.
select * from
(
SELECT 'GHSASKJK' AS T UNION ALL
SELECT 'HGGH11-ABC' UNION ALL
SELECT 'HGGH11-abc'
) f
WHERE T LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%' COLLATE Latin1_General_CS_AS
Martin Smith
2010-06-16 15:38:00
+3
A:
If the data is exactly "##-AAA", you can just use LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]'. If the data contains this sequence somewhere, use LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%'.
Note that if your column is indexed, LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]' can be a lot faster than any UFD or CLR regex, because SQL Server understands LIKE better, and it can more easily skip parts of the index if it would never match. For example, all records starting with a character outside of 0-9 will be ignored immediately, whereas a UDF or CLR regex will still read these values.
Ruben
2010-06-16 15:40:30
+4
A:
You should also be able to accomplish this with PATINDEX.
Select *
From Table
Where PatIndex( '%[0-9][0-9]-[A-Z][A-Z][A-Z]%', Value) > 0
Thomas
2010-06-16 15:40:58
+2
A:
You should be able to use a like search for this. Your where clause would be similar to:
Where YourColumnName Like '%[0-9][0-9]-[a-z][a-z][a-z]%'
* This assumes a case insensitive collation
G Mastros
2010-06-16 15:41:14