Another Sample to Remove non-numeric Values from Columns




create table test (ControlName varchar(100))
insert into test values( 'AB123C7'),('B1234C9') 
 
 
;with processTable as (
    select  Cast(ControlNameNum as int) ControlNameNum    
    from test    
 
           cross apply (
            select (select C + ''
            from (select N, substring(ControlName, N, 1) C from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) Num(N)   where N<=datalength(ControlName)) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p0 (ControlNameNum)  
 
  
) 
SELECT  ControlNameNum 
FROM processTable
 
 
 
 
drop table test
--http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters

 

Create table test (col varchar(255))
Insert into test values('152/78')
,('x152/78')
,('151/77X')
,('sub152/78')
,('142/79')
,('122/91')
,('102/108') 

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

, cte as (
    select col1  from test   
           cross apply (
            select (select C + ''
            from (select N, substring(col, N, 1) C from Nums   where N<=datalength(col)) t
            where PATINDEX('%[0-9/]%',C)> 0
            order by N
            for xml path(''))
        ) p0 (col1)  
  
   
) 
SELECT distinct col1 FROM cte
  

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