How to only get non numeric part of column out

Question

I have a column called TCODE which has value like 5T, 6545H,25 and S4444. Now I want only 3 rows out of the 4 rows returned and only the following non numeric bits of those columns T,H or S. Table name is CODES .

Pseudo code Select TCODE from CODES where I strip out numeric part of those columns where a mix of numeric and non numeric exist.

Expected Results

TCODE 
T
H
S

How can I do this ?

http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server — Lamak, Apr 04 '13 at 16:02

score 1 · Accepted Answer · edited Apr 04 '13 at 16:53

1

In SQL Server, you can possibly use PATINDEX:

SELECT SUBSTRING(TCODE,PATINDEX('%[^0-9]%', TCODE),1)
FROM CODES
WHERE PATINDEX('%[^0-9]%',TCODE) > 0

edited Apr 04 '13 at 16:53

legoscia

39,593
22
116
167

answered Apr 04 '13 at 16:37

grasshopper

36
3

Welcome to Stackoverflow! I edited your post to improve the appearance of your code snippet, by indenting each line with four spaces. – legoscia Apr 04 '13 at 16:54

score 0 · Answer 2 · answered Apr 04 '13 at 16:01

The way that comes to my mind is the rather brutish:

select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(tcode, '0', ''),
                                                                               '1', 0),
                                                                       '2', 0),
                                                                '3', 0),
                                                       '4', 0),
                                               '5', 0),
                                       '6', 0),
                                '7', 0),
                         '8', 0),
                 '9', 0) as theletters
  . . .

You can put this in a subquery, and then select where theletters <> '' for the filtering.

score 0 · Answer 3 · answered Apr 04 '13 at 16:12

One alternative:

with cte as
(select 0 n, replace(tcode,'0','') tcode from codes
 union all
 select n+1 n, replace(tcode,convert(varchar,n+1),'') tcode
 from cte
 where n<9)
select tcode from cte
where n=9

(SQLFiddle here)

score 0 · Answer 4 · answered Apr 04 '13 at 16:14

You can create a function like this

CREATE FUNCTION RemoveDigits(@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[0-9]%', @strText), 1, '')
    END
    RETURN @strText
END
GO

And then use it

SELECT dbo.RemoveDigits(TCODE) TCODE
  FROM CODES
 WHERE PATINDEX('%[^0-9]%', TCODE) > 0

Output:

| TCODE |
---------
|     T |
|     H |
|     S |

SQLFiddle

How to only get non numeric part of column out

4 Answers4