0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Craig P
  • 477
  • 6
  • 12
  • I am unclear on your question. Are you asking how to split a field in SQL Server? Or are you asking if equivalent openedge functions exist in SQL? Just for the record, Postgres has split functions which make such parsing much simpler than your expression. – Gordon Linoff May 05 '19 at 12:02
  • @GordonLinoff Could you share your suggestion on how to improve my query in Postgres? – Craig P May 05 '19 at 12:33
  • . . You can ask a question, tagged with Postgres, with sample data and desired results. – Gordon Linoff May 05 '19 at 12:52

0 Answers0