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

Question

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.

Sounds like the design is the real problem, and the data should be in 2 columns multiple rows. — Thom A
– Thom A ♦, Commented Oct 17, 2022 at 6:49
I have comma separated data like this don't use data like this. This violates the most basic design rule. Use a separate table to store those values, with separate columns for separate attributes. . You'll have to split that string into multiple rows to do what you want anyway. You're only wasting CPU time and storage space by storing strings like this — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 17, 2022 at 6:57
the data is from external source, the objecitve is to display the data. — Olivarsham
– Olivarsham, Commented Oct 17, 2022 at 7:28

jitender · Accepted Answer · 2022-10-17 06:50:33Z

2

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

answered Oct 17, 2022 at 6:50

jitender

10.4k1 gold badge21 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related