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?

Semblable

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

Effrontery

I edited the first string literal, replaced single quote inside it with double. Please correct me if I am wrong. – Falconry

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 

Pinhead

Good answer but I don't think the [5] non-alphanumeric or space picks up values that has spaces. Have put in a reply below also. Thanks tho .. – Verdi

Answer 3

To detect and show non AlphaNumeric char cases including values that contain space(s), I'm thinking of something like this:

SELECT Concat( '>', YourColumnName, '<') 
FROM YourTableName
WHERE
(
PATINDEX('%[^A-Z0-9]%',UPPER(YourColumnName) )>0 
)

Verdi