Repeating characters in T-SQL LIKE condition

Question

Problem:
Limit the value of a VARCHAR variable (or a column) to digits and ASCI characters, but allow variable length.
This script will not yield required result:

declare @var as VARCHAR (150)
select @var = '123ABC'

if (@var LIKE '[a-zA-Z0-9]{0,150}')
    print 'OK'
else
    print 'Not OK'

Anyone have idea how to do this?

Meff · Accepted Answer · 2012-09-03T11:10:05.480

4

You can do this with the not carat ^, and a NOT LIKE expression.

So you say, where not like not non-alphanumeric ;) This works for standard numbers & characters:

declare @var as VARCHAR (150)
select @var = '123ABC'

if (@var NOT LIKE '%[^a-zA-Z0-9]%')
    print 'OK'
else
    print 'Not OK'

Edit: Thanks Martin for the collation hint, if you want the characters like ý treated as non-alphanumeric add in the COLLATE as below

declare @var as VARCHAR (150)
select @var = '123ABCý'

if (@var NOT LIKE '%[^a-zA-Z0-9]%' COLLATE Latin1_General_BIN ) 
    print 'OK'
else
    print 'Not OK'

edited Sep 03 '12 at 11:10

answered Sep 03 '12 at 10:46

Meff

5,889
27
36

You might need a binary collate clause in there as well. Without it it can return additional characters `SELECT number, CHAR(number) FROM master..spt_values WHERE type='P' AND number between 0 and 255 and CHAR(number) NOT LIKE '%[^a-z^A-Z^0-9]%'` – Martin Smith Sep 03 '12 at 10:52
@MartinSmith thanks, hadn't considered the odd characters, they didn't crop up for me when I had to do this years ago, but they could for the asker. Many thanks. – Meff Sep 03 '12 at 11:01
Also the pattern needs to be `NOT LIKE '%[^a-zA-Z0-9]%'`. Without that the caret matches. With it the earlier query brings back 62 rows as expected. – Martin Smith Sep 03 '12 at 11:03
@MartinSmith thanks again, fixed. My word you have a lot of points :) – Meff Sep 03 '12 at 11:10
Just another thing... Any idea how to extend the list of allowed characters with special ones ([]%^*)? How to combine caret (^) and ESCAPE characters? Thanks a lot. – Nikola Sep 03 '12 at 12:54
Forget the question. This works fine: if (@var NOT LIKE '%[^a-zA-Z0-9!@!.!_!:!-!%![]%' ESCAPE '!') – Nikola Sep 03 '12 at 13:21

Niladri Biswas · Answer 2 · 2012-09-03T11:27:01.403

Will this help

Declare @t table (Alphanumeric VARCHAR(100))
Insert Into @t 
Select '123ABCD' Union All Select 'ABC' Union All 
Select '123'  Union All  Select  '123ABCý' Union All
Select 'a-z123' Union All  Select 'abc123' Union All
Select 'a1b2c3d4'


SELECT Alphanumeric
FROM @t 
WHERE Alphanumeric LIKE '%[a-zA-Z0-9]%' 
AND ( Alphanumeric NOT LIKE  '%[^0-9a-zA-Z]%' COLLATE Latin1_General_BIN) 
AND LEN(Alphanumeric)> 6 -- display records having more than a length of 6

//Result

Alphanumeric

123ABCD
a1b2c3d4

N.B.~ Used Martin's collation hint..Thanks

score 0 · Answer 3 · answered Sep 03 '12 at 10:42

0

T-SQL doesn't support RegEx.

You can use SQL CLR to run such expression though.

Also try the LEN function:

if (LEN(@var) <= 150)
    print 'OK'
else
    print 'Not OK'

answered Sep 03 '12 at 10:42

abatishchev

98,240
88
296
433

score 0 · Answer 4 · edited Oct 01 '20 at 18:08

0

T-SQL doesn’t support regex, closest you can get is the PATINDEX function that you can use to match specific characters, but you can’t specify the count. You can try combining it with the LEN function to check the length.

See this page for a few examples of PATINDEX.

edited Oct 01 '20 at 18:08

ib.

27,830
11
80
100

answered Sep 03 '12 at 10:45

Fedor Hajdu

4,657
3
32
50

Repeating characters in T-SQL LIKE condition

4 Answers4

Linked