Menu
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.
981
LIKE clause is used to perform the pattern matching task in SQL. A WHERE clause is generally preceded by a LIKE clause in an SQL query. LIKE clause searches for a match between the patterns in a query with the pattern in the values present in an SQL table.
SQL pattern matching can be done using the LIKE operator. _ is used to match a single element and % is used to match an arbitrary number of elements. The NOT operator is used to select elements that do not match the given pattern.
The SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters.
The LIKE operator is used in the WHERE condition to filter data based on some specific pattern. It can be used with numbers, string, or date values.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
