CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-like
user234872
user234872

Reputation: 215

Create SQL 'LIKE' Statment that looks for specific Patterns of numbers and letters

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.

Upvotes: 7

Views: 72907

Answers (5)

Chris Klepeis
Chris Klepeis

Reputation: 9973

I would recommend creating a CLR assembly with .Net. That way you can create a function or SP that can use regex.

http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

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

Upvotes: 2

Ruben
Ruben

Reputation: 15505

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.

Upvotes: 4

George Mastros
George Mastros

Reputation: 24498

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

Upvotes: 2

Thomas
Thomas

Reputation: 64635

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

Upvotes: 7

Martin Smith
Martin Smith

Reputation: 452977

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

Upvotes: 15

Related Questions