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?

Answer 1

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'  

Answer 2

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'  

Answer 3

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

Answer 4

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

EDIT: Check this page for a few examples of PATINDEX.