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

Question

I am trying to filter some SQL server data and require results with the following conditions:

• where the field has alphanumeric characters, case insensitive
• where the field has certain punctuation (apostrophe and hyphen)
• where the field has no spaces

Is there an efficient way to do this using CHAR in SQL Server or does anybody have a better solution?

Answer 1

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-']%'

Answer 2

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

--Tmp table
DECLARE @regexpTbl TABLE([accountNo] nvarchar(200));

--Test rows
insert 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 ('$')

--Everything
SELECT accountNo as [1] FROM @regexpTbl 

--does not have non-alphnumeric
--i.e has alphanumeric only
SELECT accountNo as [2] FROM @regexpTbl WHERE  accountNo NOT LIKE '%[^a-z0-9-'' ]%'

--has at least one alphanumeric
SELECT accountNo as [3] FROM @regexpTbl WHERE accountNo LIKE '%[a-Z]%' 

--has non-alphanumeric or space
SELECT accountNo as [5] FROM @regexpTbl WHERE PATINDEX('%[^a-zA-Z0-9 ]%',accountNo)>0 

--does not have non-alphnumeric
--i.e has alphanumeric only
SELECT accountNo as [6] FROM @regexpTbl WHERE PATINDEX('%[^a-zA-Z0-9]%',accountNo)<=0