Add a character in a string at certain location based on logic in SQL Server-CodePudding

I have comma separated data like this in one of the column

48FGTG,100ERTD,18NH,07EWR,9FDC,2POANAR,100GTEDC  
46FGTG,78ERTD,67NH,76EWR,3FDC

The numbers in the starting is percentage, whatever comes after the first alphabetic character is percentage, it varies from 0-100.

I have to update the data like

48% FGTG,100% ERTD,18% NH,07% EWR,9% FDC,2% POANAR,100% GTEDC  
46% FGTG,78% ERTD,67% NH,76% EWR,3% FDC

I can filter out the percentile in regex, but not sure using it in SQL. Any lead would be helpful.

CodePudding user response：

You can do it like

select  STRING_AGG(substring(value,0,PATINDEX('%[^0-9]%',value)) '%' substring(value,PATINDEX('%[^0-9]%',value),len(value)),',') from string_split('48FGTG,100ERTD,18NH,07EWR,9FDC,2POANAR,100GTEDC
46FGTG,78ERTD,67NH,76EWR,3FDC',',')

Here's what I have done

1.Use PATINDEX to find the first occurrence of character

2.Use substring function to extract the first number and then remaining string

3.Use STRING_AGG to concatenates the values of string expressions and places separator values between them