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

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