[Stackoverflow] [Progress OpenEdge ABL] MS SQL - Split field [duplicate]

Status
Not open for further replies.
C

Craig P

Guest
This question already has an answer here:


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

An example of the field:

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. Is there a 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...
 
Status
Not open for further replies.
Top