Splitting a string by CRLF in SQL-CodePudding

SO I know that there are a few post like this already here, but those post don't seem to work for me. My string contains '^' in it, and replace does not seem to like that character. Because of this, SELECT * FROM STRING_SPLIT(REPLACE(@InbMsg,@CLRF,'|'),'|') does not seem to work The example message is this:

'W^1^Wave1102^2^11 

H^12345678900987654321^OD1128263^MLO^7^Bill’s Order^98712391^N^A2^3^11

D^OGMens77162^123456789009^Y^4^Medium^Red^006134^000101^11728492'

When I run it through the previous statment, I just get 'W'. Anyone know why?

CodePudding user response：

You want to replace the newline between 11 and H and also between 11 and D. You can replace the newline by looking for CHAR(10) then replace it with '^'. Then you can now split the rows using '^' as delimiter.

select * from STRING_SPLIT(REPLACE('W^1^Wave1102^2^11 
H^12345678900987654321^OD1128263^MLO^7^Bill’s Order^98712391^N^A2^3^11
D^OGMens77162^123456789009^Y^4^Medium^Red^006134^000101^11728492',CHAR(10),'^'),'^');
 

Sample result:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0dc110c889af42a8b02096eedee3c393

value
W
1
Wave1102
2
11 
H
12345678900987654321
OD1128263
MLO
7
…
10 rows of 26

CHAR(10) is ascii value for New Line / Line Break for SQL Server

CodePudding user response：

It was a situation of not using varchar correctly. Make sure to declare variables correctly! ```SELECT * FROM STRING-SPLIT(REPLACE(@msg, @CLRF, ','),',') works in this situation!