SQL Server 2005 - Stripping Only Alpha Values from String

Question

I am using SQL Server 2005 and I want to extract the alpha part of a string.

i.e.

From ABC123, I would like to get ABC

From AB1234, I would like to get AB. etc etc.

What is the easiest way to do this?

Answer 1

You could make a user defined function that would loop through the characters and build up a new string where the characters were letters.

Answer 2

if there is always a space between the letter and the digits, try:

DECLARE @String  varchar(100)
SET @String='ABC 123'

SELECT LEFT(@String,LEN(@String)-CHARINDEX(' ',@String))

OUTPUT

-------------------
ABC

(1 row(s) affected)

EDIT after OP's comment, assumes no space before digits:

DECLARE @String  varchar(100)
SET @String='ABCD123'

;with Numbers AS
(
    SELECT 1 AS Number,ISNUMERIC(SUBSTRING(@String,1,1)) AS Digit
    UNION ALL
    SELECT Number+1,ISNUMERIC(SUBSTRING(@String,Number+1,1)) AS Digit
        FROM Numbers
    WHERE Number<LEN(@String)
)
SELECT LEFT(@String,MAX(Number)) FROM Numbers WHERE Digit=0
--OPTION (MAXRECURSION n)  --if the string is longer than 100 characters uncomment this and set "n" to the string length

OUTPUT:

-------------------
ABCD

(1 row(s) affected)

Answer 3

You also might consider doing this not in SQL but somewhere else, if it is possible in your scenario. If you explain what kind of data are you working with how they get into your sql database and what and how uses them it might be possible to suggest a better alternative.