Using SELECT to obtain only fields with alphanumeric data (and certain punctuation) in SQL Server

This uses a double negative to filter only to the desired range of characters

Any character outside the desired range gives true from LIKE. If the string consists only of character in the desired range, LIKE gives false. Then another NOT

WHERE   SomeCol NOT LIKE '%[^a-z0-9-'']%'

Note: I used single quote here

By default, SQL Server is case insensitive. Add a COLLATE clause if needed

   SomeCol COLLATE Latin1_General_CI_AS NOT LIKE '%[^a-z0-9-']%'

or change the range

   SomeCol NOT LIKE '%[^a-zA-Z0-9-']%'

or, if you want to include ä = a

   SomeCol COLLATE Latin1_General_CI_AI NOT LIKE '%[^a-z0-9-']%'

RegExps as per @a-k, but using the PATINDEX and testing with temp table.

--Tmp tableDECLARE @regexpTbl TABLE([accountNo] nvarchar(200));--Test rowsinsert into @regexpTbl (accountNo) values ('AAA')insert into @regexpTbl (accountNo) values ('1111')insert into @regexpTbl (accountNo) values ('AA11ASD')insert into @regexpTbl (accountNo) values ('AA1-1ASD')insert into @regexpTbl (accountNo) values ('$$$$$$$')insert into @regexpTbl (accountNo) values ('$$$AAA AA$$$$')insert into @regexpTbl (accountNo) values ('A')insert into @regexpTbl (accountNo) values ('$')--EverythingSELECT accountNo as [1] FROM @regexpTbl --does not have non-alphnumeric--i.e has alphanumeric onlySELECT accountNo as [2] FROM @regexpTbl WHERE  accountNo NOT LIKE '%[^a-z0-9-'' ]%'--has at least one alphanumericSELECT accountNo as [3] FROM @regexpTbl WHERE accountNo LIKE '%[a-Z]%' --has non-alphanumeric or spaceSELECT accountNo as [5] FROM @regexpTbl WHERE PATINDEX('%[^a-zA-Z0-9 ]%',accountNo)>0 --does not have non-alphnumeric--i.e has alphanumeric onlySELECT accountNo as [6] FROM @regexpTbl WHERE PATINDEX('%[^a-zA-Z0-9]%',accountNo)<=0