SQL - How to split a string with multiple instances of the same string, separated by carriage returns? [duplicate]

Question

This is the data I have, it's separated by carriage returns.

'AM 1492       AM 1791       PM 1492       PM 1791       '

Desired results:

How do I get each piece of data on its own row? I've tried CharIndex to identify the character and I was thinking of using a substring, but I'm not sure how to separate each item into a row when there are multiple instances of a string like 'AM', 'AM', etc.

CREATE TABLE #T1
(
    StringInfo VARCHAR (500)
)

INSERT INTO #T1 (StringInfo)
VALUES ('AM 1492       AM 1791       PM 1492       PM 1791       ')

SELECT * 
FROM #T1 AS T

Doesn't look like it's separated by a carriage return, just a bunch of spaces. — JNevill
– JNevill, Commented Sep 1, 2022 at 19:38
CROSS APPLY STRING_SPLIT(REPLACE(T.StringInfo, ' ', '|'), '|') should do the trick — Charlieface
– Charlieface, Commented Sep 1, 2022 at 21:12

JNevill · Accepted Answer · 2022-09-01 19:42:46Z

2

If it truly is a carriage return then the following should do the job:

CREATE TABLE #T1
(
    StringInfo NVARCHAR (500)
)

INSERT INTO #T1 (StringInfo)
VALUES ('AM 1492
AM 1791
PM 1492
PM 1791')

SELECT value
FROM #T1 cross apply string_split(StringInfo, Char(13)) as p

value
AM 1492
AM 1791
PM 1492
PM 1791

answered Sep 1, 2022 at 19:42

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

1 Comment

JM1 Over a year ago

Thanks, @JNevill! I appreciate you taking the time to help me! Your answer worked for me!

Collectives™ on Stack Overflow

SQL - How to split a string with multiple instances of the same string, separated by carriage returns? [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related