Strip non-numeric character in a column




create table test (t_id int primary key, col varchar(50))
INSERT INTO test
VALUES      (1,'462'),
            (2,'E08R'),
            (3,'E07'),
            (4,'E09'),
            (5,'E06'),
            (6,'360'),
            (7,'E04'),
            (8,'E11R'),
            (9,'E02R') 

 

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 
processTable as (
    select col,Cast(col1 as int) col1   from test
        cross apply (
            select (select C + ''
            from (select n, substring(Col, n, 1) C 
			FROM Num1  where n<=datalength(Col)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)

		)

SELECT col,col1 FROM processTable
 
 --http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


--Another option
;with
mycte1 as
(select  col, REPLACE(col,SUBSTRING(col,PATINDEX('%[^0-9]%',col),1),'') col1 from test )
,mycte2 as (
select col, REPLACE(col1,SUBSTRING(col1,PATINDEX('%[^0-9]%',col1),1),'') col2 from mycte1)
,mycte3 as (
select col, REPLACE(col2,SUBSTRING(col2,PATINDEX('%[^0-9]%',col2),1),'') col3 from mycte2)
,mycte4 as (
select col, REPLACE(col3,SUBSTRING(col3,PATINDEX('%[^0-9]%',col3),1),'') col4 from mycte3)

Select col, Cast(col4 as int) from mycte4

drop table test

--Another example
create table test (COLUMN_A varchar(100))
INSERT INTO test VALUES
('AAAA AAAAAAA CO A.A. # 4030'),      	
('BBBBBB BBB BBB CO. #  4260.'),  	
('CCC CCCCC #3001'),
('CCC CCCCC 3001')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1), 

processTable as (

select Substring(COLUMN_A, 0,Charindex('#',  COLUMN_A)) COLUMN_A , Cast(col1 as int) COLUMN_B   from test
        cross apply (
            select (select C + ''
            from (select n, substring(COLUMN_A, n, 1) C 
            FROM Num1  where n<=datalength(COLUMN_A)) [1]
            where PATINDEX('%[0-9]%',C)> 0
            order by n
            for xml path(''))
        ) p (Col1)
 
        )

 Select * from processTable

 Drop table test

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s