CODEWITHSUNDEEP
sqlsql-servert-sql
Tibomso
Tibomso

Reputation: 373

LIKE operator as a replacement for RegEx

My task is to select entries for the following mask - SNNN000 Where:

Here's what I got - "[0-9A-Za-z][0-9][0-9][0-9][0-9A-Za-z][0-9A-Za-z][0-9A-Za-z]". There was a problem with "0", how can I make it so that the mask can ignore the conditions for this symbol? All entries except the 5th(Id) from the @table table should be displayed.

DECLARE @table TABLE (
    id      INT
    ,Txt    NVARCHAR(100)
);

INSERT  INTO @table (id, Txt)
VALUES (1, N'S123AB1')
,(2, N'S123')
,(3, N'S123A')
,(4, N'S123AB')
,(5, N'S123.@!');

SELECT * 
FROM @table AS t
WHERE t.Txt LIKE N'[0-9A-Za-z][0-9][0-9][0-9][0-9A-Za-z][0-9A-Za-z][0-9A-Za-z]'

I understand that I could add conditions via the OR operator. But I would like to do it in a single expression and I could do it in regular expressions "[0-9A-Za-z]\d{3}[0-9A-Za-z]?[0-9A-Za-z]?[0-9A-Za-z]?". As I understand it, there are no full regular expressions in SQL, if I am wrong, then I would appreciate an explanation.

SELECT  *
FROM    @table AS t
WHERE   t.Txt LIKE N'[0-9A-Za-z][0-9][0-9][0-9][0-9A-Za-z][0-9A-Za-z][0-9A-Za-z]'
        OR  t.Txt LIKE N'[0-9A-Za-z][0-9][0-9][0-9]';

Upvotes: 0

Views: 386

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The simplest method I can think of is:

SELECT t.*
FROM @table AS t
WHERE (t.Txt + 'AAA') LIKE '[0-9A-Za-z][0-9][0-9][0-9][0-9A-Za-z][0-9A-Za-z][0-9A-Za-z]%' AND
      LEN(t.Txt) BETWEEN 4 AND 7;

This adds three extra characters and checks that the first 7 characters match. It then validates the length of the column.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Unfortunately, using OR is probably the best you can do using SQL Server's enhanced LIKE operator:

SELECT * 
FROM @table AS t
WHERE
    t.Txt LIKE N'[0-9A-Za-z][0-9][0-9][0-9]' OR
    t.Txt LIKE N'[0-9A-Za-z][0-9][0-9][0-9][0-9A-Za-z]' OR
    t.Txt LIKE N'[0-9A-Za-z][0-9][0-9][0-9][0-9A-Za-z][0-9A-Za-z]' OR
    t.Txt LIKE N'[0-9A-Za-z][0-9][0-9][0-9][0-9A-Za-z][0-9A-Za-z][0-9A-Za-z]';

Upvotes: 2

Related Questions