CODEWITHSUNDEEP
sql-serverms-access
Kristen Waite
Kristen Waite

Reputation: 1465

Setting length of LIKE in SELECT with regex?

I have an Access SQL query that includes a select on a text like

SELECT column FROM table WHERE column LIKE "abc#####";

where the value to be found has a string length of 8 characters, begins with "abc" and ends with 5 numbers.

I am attempting to translate this into a SQL Server (2005) query on a varchar column and first came up with this:

SELECT column FROM table WHERE column LIKE 'abc_____';

But of course this matches values with non-numerics in the last 5 characters. So instead I've been using the following:

SELECT column FROM table WHERE column LIKE 'abc[0-9][0-9][0-9][0-9][0-9]';

This works, but is there a more efficient/accepted way to handle this type of select? (Is there another special character that works like the underscore _ but for numerics?)

Upvotes: 0

Views: 1690

Answers (2)

Joseph B
Joseph B

Reputation: 5669

Try this:

SELECT column FROM table
WHERE substring(column, 1, 3) = 'abc'
AND substring(column, 4, LEN(column)-3) NOT LIKE '%[^0-9]%'

References:

Repeating characters in T-SQL LIKE condition on SO

Upvotes: 0

Anon
Anon

Reputation: 10908

DECLARE @pattern varchar(max) = 'abc#####'

SET @pattern = REPLACE(@pattern,'#','[0-9]')
SELECT column FROM table WHERE column LIKE @pattern

Upvotes: 1

Related Questions