Remove String and Symbols-CodePudding

I am trying to create a query to remove some varchar and symbols from a big string, basically a table will have a column with this format(the information comes from an API call):

$.owner = "[email protected]" and $.asignee ="joe" and $.Entities.Entity = "12345" And $.CountryService.Country ="1" and $.CountryService.Service="B"

so the requirement is to take the main "column names" from the sample, so at the end the string will be like:

    owner = "[email protected]" and asignee ="joe" and Entity = "12345" And Country ="1" and Service="B"

this should be dynamic because we could have more data like $.Entities.Name, $.CountryService.Region, etc

CodePudding user response：

This is rather simple and can be done leveraging STRING_SPLIT, STRING_AGG, and CHARINDEX.

DECLARE @string VARCHAR(1000) = 
  '$.owner = "[email protected]" and $.asignee ="joe" and $.Entities.Entity = "12345" And $.CountryService.Country ="1" and $.CountryService.Service="B"';

SELECT NewString = 
         STRING_AGG(SUBSTRING(split.value,IIF(p.P1>0 AND p.P2>p.P1,p.P1 1,1),8000),'and ')
FROM        STRING_SPLIT(REPLACE(REPLACE(@string,'$.',''),'and ','|'),'|')   AS split
CROSS APPLY (VALUES(CHARINDEX('.',split.value), CHARINDEX('"',split.value))) AS p(P1,P2);

Results:

owner = "[email protected]" and asignee ="joe" and Entity = "12345" and Country ="1" and Service="B"