Remove text phrases from SQL Server column -
i want change string :
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 words without number. send me request:
select string_agg(wyraz, ' ') 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) wyraz~'[0-9]' but i'm not sql , want have in request
update [table] set [column] = can help?
in sql server 2016+ can use string_split() , in sql server 2017+ use string_agg().
in sql server pre-2016, using csv splitter table valued function jeff moden:
along using stuff() select ... 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 dbo.[delimitedsplit8k](@str,' ') s s.item '%[0-9]%' order s.itemnumber 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 update on table:
create table t (col varchar(8000)) insert 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 dbo.[delimitedsplit8k](t.col,' ') s s.item '%[0-9]%' order s.itemnumber xml path (''), type).value('.','nvarchar(max)') ,1,1,'') splitting strings reference:
Comments
Post a Comment