0

I'm thinking this is easy, but I'm having a hard time manifesting. I have a list of values, e.g.

@bob
@sarah
@kelly;john
@denver_colorado
@james12;kellybelly

I want the list to be:

@bob
@sarah
@kelly
@john
@denver_colorado
@james12
@kellybelly

essentially, splitting a line after ";" and add @ as a prefix to all values

1
  • Hello Teddy. In order to help people more effectively help you, please always specify was version and edition of SQL Server (or any platform / software) you are using, and please also indicate if the initial list of values is a single string of multiple lines or multiple rows as it is currently ambiguous. Thanks. Commented Aug 20, 2020 at 13:44

1 Answer 1

1

Use the SPLIT_STRING function to separate the string on each ';' Use the REPLACE function to change all '@' to a ';' so this also signals a split. Prefix the value with '@'

declare @s nvarchar(max) 

set @s = '@bob
@sarah
@kelly;john
@denver_colorado
@james12;kellybelly'

SELECT '@' + value
FROM SPLIT_STRING(REPLACE(@string, '@', ';'), ';')

2
  • it works only on SQL2016 +. If you are using an older version, you will have to do some kind of loops to do the same thing Commented Aug 20, 2020 at 12:41
  • Hello Daniel. This is a good start, but it's important to test your solution (or if that's not possible, then at least indicate that it is untested). I mention this because there are two significant syntax errors that prevent the code from even running. Additionally, once those two things are fixed, the results include an extra empty row, which is probably undesirable. Finally, you are assuming that the initial list of values is a single string instead of individual rows, which is how I interpreted it. Since it is unclear, I posted a question to the OP asking for clarification. Commented Aug 20, 2020 at 13:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.