Remove number from a column (T-SQL)


  

   CREATE TABLE Contacts
    (
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    MiddleName NVARCHAR(100),
    EmailAddress NVARCHAR(100)
    )

    INSERT INTO Contacts
    VALUES ('Abigail','K','Gonzalez','abigail0@adventure-works.com'),
           ('Michael','NULL','Graff','michael16@adventure-works.com')

--===== Create number table on-the-fly
;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)

,processTable as (
    select  EmailAddress, col  as Number_removed_EmailAddress   
    from Contacts Cross Apply (
            select (select C + ''
            from (select N, substring(EmailAddress, N, 1) C from Nums  
			where N<=datalength(EmailAddress)) t
            where PATINDEX('%[^0-9]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ) p0 (col)  

) 
SELECT  EmailAddress,   Number_removed_EmailAddress   FROM processTable

drop table Contacts

 

https://social.msdn.microsoft.com/Forums/en-US/5a43eb63-5f2e-428b-90f6-c2163590ff01/query-help-please?forum=transactsql

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