CODEWITHSUNDEEP
sql-servert-sql
Nick
Nick

Reputation: 5882

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

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

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

Upvotes: 11

Views: 43062

Answers (3)

Allan F
Allan F

Reputation: 2298

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 
)

Upvotes: 0

OzBob
OzBob

Reputation: 4520

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

Upvotes: 1

gbn
gbn

Reputation: 432271

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

Upvotes: 22

Related Questions