SQL Server : split column [duplicate]

This question already has an answer here:

• how to separate string into different columns? 5 answers
• How to split a comma-separated value to columns 33 answers

In a SQL Server database, I have a column CONTACT_VALUE which contains fields with 0, 1 or 2 CHR(10) characters in it.

An example of the column value:

BLOCK B MANTAGE PARKCHR(10)MONTAGUE GARDEN WESTERN CAPECHR(10)PLATTEKLOOF ROAD


I would like to output:

Column 1 = BLOCK B MANTAGE PARK
Column 2 = MONTAGUE GARDEN WESTERN CAPE
Column 3 = PLATTEKLOOF ROAD


I have the same data in a Progress OpenEdge DB and I obtain the results with the following code but I have not been able to replicate it in SQL Server.

Is there a T-SQL equivalent for PRO_ELEMENT and PRO_ARR_DESCAPE?

{FN PRO_ARR_DESCAPE(PRO_ELEMENT(REPLACE(PRO_ARR_ESCAPE(CONTACT_VALUE), CHR(10), ';'), 1,1)) },
CASE WHEN(LENGTH(REPLACE(CONTACT_VALUE,CHR(10), '11')) - LENGTH(CONTACT_VALUE)) < 1 THEN '' ELSE { FN UPPER(PRO_ARR_DESCAPE(PRO_ELEMENT(REPLACE(PRO_ARR_ESCAPE(CONTACT_VALUE), CHR(10), ';'), 2,2))) } END,
CASE WHEN(LENGTH(REPLACE(CONTACT_VALUE,CHR(10), '11')) - LENGTH(CONTACT_VALUE)) < 2 THEN '' ELSE { FN UPPER(PRO_ARR_DESCAPE(PRO_ELEMENT(REPLACE(PRO_ARR_ESCAPE(CONTACT_VALUE), CHR(10), ';'), 3,3))) } END,


Many thanks!

Continue reading...