0

I have to concatenate around 35 Columns in a table into a single string. The data within a column can be repetitive with different case, as per the below.

COL_1
apple | ORANGE | APPLE | Orange 

COL_2
GRAPE | grape | Grape

The data in each column is pipe separated and I am trying to concatenate each column by separating with '|'. I expect the final output to be "apple | orange | grape" (All in lower case is fine)

But currently I am getting

apple | ORANGE | APPLE | Orange | GRAPE | grape | Grape

My current SQL is 

SELECT COL_1 || '|' || COL_2 from TABLE_X;

Can some one explain me how to extract unique value from each column? This will reduce my string length drastically. My current SQL is exceeding Oracle's 4000 character limit.

TechyHarry
  • 301
  • 2
  • 8
  • 25
  • 1
    Can you show us what you are using to get that result? In Oracle SELECT DISTINCT LOWER(column) FROM table will work to sort out the unique lower case values. – Frazz May 25 '14 at 21:40
  • 1
    Your problem is that it's not normalised. The simplest way to do this would be to unpivot it before concatenating, ensure that the data-set is unique, then to use a string aggregation function like `LISTAGG()` to concatenate it back up. Your question is tagged SQL Server _and_ Oracle. Do you need this to work on both or only on Oracle? – Ben May 25 '14 at 21:52
  • Hi Ben,I need this to work only in Oracle. Approved your suggestions. – TechyHarry May 25 '14 at 21:55
  • Hi Frazz, DISTINCT LOWER(COLUMN) will eliminate distinct across columns. I want to eliminate duplicates within the column. The text in a column is separated by | and is different in case with same values. I want to extract only unique value from each column. Updated my example – TechyHarry May 25 '14 at 21:56
  • Huh!! Oops a typo!!! Yes it is "apple | orange | grape" – TechyHarry May 25 '14 at 22:05
  • You are storing lists of things in a pipe delimited list. This is a very, very bad idea. SQL supports tables for storing lists, not strings. I would strongly suggest that you change your data structure. – Gordon Linoff May 26 '14 at 01:20
  • Hi Gordon, Thanks for your response. Yes its true that data structure is not good. Since its a legacy system with huge data, I don't have any other option rather to extract from it. – TechyHarry May 26 '14 at 16:07

1 Answers1

0

I tried doing this




WITH test AS
  ( SELECT 'Test | test | Test' str FROM dual
  )
SELECT *
FROM
  (SELECT DISTINCT(LOWER(regexp_substr (str, '[^ | ]+', 1, rownum))) split
  FROM test
    CONNECT BY level <= LENGTH (regexp_replace (str, '[^ | ]+')) + 1
  )
WHERE SPLIT IS NOT NULL;

This query produces only 'test'

Some how its producing unique values after splitting the string separated by ' | ' in a column. But doing this for 35+ columns in a single SQL query would be cumbersome. Could someone suggest a better approach?

TechyHarry
  • 301
  • 2
  • 8
  • 25
  • You don't need to do this 35 times... if you concatenate everything together beforehand then this will do it accurately. Use the OPs example rather to see. You can then use `LISTAGG()` to re-aggregate back up... there really isn't a better way save for changing the database completely. The problem with this method is that it's going to be _horrible_ if the table has more than one row. – Ben May 25 '14 at 22:14
  • Concatenating all the 35 columns is exceeding oracle limit of 4000 characters and getting following error ORA-12801: error signaled in parallel query server P014, ORA-01489: result of string concatenation is too long – TechyHarry May 25 '14 at 22:18
  • Hi Ben, Any other solution which solves the problem? – TechyHarry May 25 '14 at 23:15