Remove certain characters from a string


Userlevel 4

Goal

Sometimes you need to send a XML to an external company, an XML cannot handle diacritics and special characters such as & and ^.

Solution

declare @string as varchar(max) = 'A sentence with ïllïgâl characters, such âs ^ and &.'

--Defines which characters to keep, in a regex.
declare @keepvalues as varchar(max) = '%[^a-za-z0-9/-?:( ).,''+]%'

--Remove the characters that are not in the regex of @keepvalues
while patindex(@keepvalues, @string) > 0
set @string = stuff(@string, patindex(@keepvalues, @string), 1, '')

--Collate to remove diacritic (â and such)
select replace(replace(@string,'>',')'),'<','(') collate japanese_bushu_kakusu_100_cs_as_ks_ws

 Result

'A sentence with illigal characters, such as  and .'

 


0 replies

Be the first to reply!

Reply