Search code examples
sqlsql-serversql-server-2014

Remove text phrases from SQL Server column

I want to change string like :

UD12679S ASPL 0001362701 BOSCH Lista EAA152325 EAA 254336 ELSTOCK 01179470 KHD 1179470 KHD LRS02664 LUCAS 560004113 PSH 12030287 ROBERT'S

to

UD12679S 0001362701 EAA152325 254336 01179470 1179470 LRS02664 560004113 1203028

so remove just words without any number. Someone send me this request:

SELECT 
    string_agg(wyraz, ' ')  
FROM 
    unnest(string_to_array('UD12679S ASPL 0001362701 BOSCH Lista EAA152325 EAA 254336 ELSTOCK 01179470 KHD 1179470 KHD LRS02664 LUCAS 560004113 PSH 12030287 ROBERT''S'::text, ' ')) x(wyraz) 
WHERE 
    wyraz~'[0-9]'

but I'm not good with SQL and I want to have it in request like 

UPDATE [table] 
SET [column] =

Can someone help?

Solution

  • In SQL Server 2016+ you can use string_split() and in SQL Server 2017+ you also use string_agg().

    In SQL Server pre-2016, using a CSV Splitter table valued function by Jeff Moden:

    Along with using the stuff() with select ... for xml path ('') method of string concatenation. 

    declare @str varchar(8000) = 'UD12679S ASPL 0001362701 BOSCH Lista EAA152325 EAA 254336 ELSTOCK 01179470 KHD 1179470 KHD LRS02664 LUCAS 560004113 PSH 12030287 ROBERT''S';

select stuff (
  (
  select ' '+s.Item
    from dbo.[delimitedsplit8K](@str,' ') s
    where s.Item like '%[0-9]%'
    order by s.ItemNumber
  for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')

    rextester demo: http://rextester.com/TSXC15231

    returns: 

    D12679S 0001362701 EAA152325 254336 01179470 1179470 LRS02664 560004113 12030287

    For an update on a table:

    create table t (col varchar(8000))
insert into t values ('UD12679S ASPL 0001362701 BOSCH Lista EAA152325 EAA 254336 ELSTOCK 01179470 KHD 1179470 KHD LRS02664 LUCAS 560004113 PSH 12030287 ROBERT''S')

update t
set col = stuff (
  (
  select ' '+s.Item
    from dbo.[delimitedsplit8K](t.col,' ') s
    where s.Item like '%[0-9]%'
    order by s.ItemNumber
  for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')

    splitting strings reference: