A Sample to remove non-numeric values from columns



CREATE TABLE [dbo].[DecoderModels](
	[PK_DecoderModelID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
	[ModelName] [nvarchar](50) NOT NULL,
	[SN_First] [varchar](50) NOT NULL,
	[SN_Last] [varchar](50) NOT NULL)

declare @sn varchar(50)='aao255'

Insert into [DecoderModels] Values 
('aaa','om000001','om499999')
,('bbb','0001','9999')
,('ccc','aao000001','aao9999999')

;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 [SN_First], [SN_Last]
	, ISNULL(Cast(Col1 as int),0) as Col1
	, ISNULL(Cast(Col2 as int),99999999) as Col2
	, Cast(Col3 as int) Col3
	,Col4
    from [DecoderModels]
        cross apply (
            select (select C + ''
            from (select N, substring([SN_First], N, 1) C from Num1  where N<=datalength([SN_First])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p (Col1)

		   cross apply (
            select (select C + ''
            from (select N, substring([SN_Last], N, 1) C from Num1  where N<=datalength([SN_Last])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p2 (Col2)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p3 (Col3)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[^0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p4 (Col4)

 
)

SELECT [SN_First], [SN_Last], Col1,Col2,Col3,col4
FROM processTable
WHERE Cast(Col3 as int) Between cast(Col1 as int) And cast(Col2 as int) 
AND ([SN_First] Like Col4+'%' AND  [SN_Last]  Like Col4+'%') Or (IsnuMeric([SN_First])=1 And Isnumeric([SN_Last])=1 And Col4 Is NUll)



drop table [DecoderModels]


 

http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters

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