CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Boris
Boris

Reputation: 1210

Extract uppercase letters and numbers

I'm trying to process a string so that only uppercase letters and digits are taken. Trying to use PATINDEX because I want to keep this concise - it will be part of a bigger query.

There are quite a few examples out there showing something similar, however I seem to be missing something. So far I've got

DECLARE @string varchar(100)

SET @string = 'AbcDef12-Ghi'

WHILE PATINDEX('%[^A-Z0-9]%',@string) <> 0
    SET @string = STUFF(@string,PATINDEX('%[^A-Z0-9]%',@string),1,'')

SELECT @string

As per e.g. this question, however this doesn't seem to be doing what I need.

Using [^0-9] outputs 12, which is correct.

However [^A-Z] outputs AbcDefGhi, i.e. both uppercase and lowercase. Doesn't matter if I use [^A-Z] or [^a-z], both return all letters.

I think I may be missing something simple? I found a few suggestions referring to collation and tried to use COLLATE with PATINDEX as per this but couldn't get it to work.

Note: I'm doing this as a one-off query on a SQL Server database to find some data - this will not be reused anywhere else so things like performance, SQL injection (mentioned quite frequently in this context) etc. are not a concern.

Upvotes: 3

Views: 1990

Answers (1)

DavidG
DavidG

Reputation: 119146

You can do this using COLLATE and the a case sensitive collation such as Latin1_General_BIN:

DECLARE @string varchar(100)

SET @string = 'AbcDef12-Ghi'

WHILE PATINDEX('%[^A-Z0-9]%',@string COLLATE Latin1_General_BIN) <> 0
BEGIN
    SET @string = STUFF(
        @string,
        PATINDEX('%[^A-Z0-9]%',@string COLLATE Latin1_General_BIN),1,''
    )
END

SELECT @string

This will output:

AD12G

Upvotes: 8

Related Questions