SQL: split string using a separator value

Question

New to SQL. Using oracle sql developer Version 3.1.07. Is there any way to separate elements of a string column that contains a string like:

dublin.ie;montreal.com;paris.com

I want to split them based on the ; so that I can manipulate them individually (and do so for every row of the table). This is because I want to delete everything that ends in .com but not effect the rest.

I want to use the most basic sql I can, as I am quite the newbie and I'm using quite an old version.

Any ideas? Thanks again you guys. The help so far has been great.

possible duplicate of [Splitting comma separated string in a PL/SQL stored proc](http://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc) — Taryn, Sep 13 '12 at 12:01

score 2 · Accepted Answer · answered Sep 13 '12 at 12:49

I'd say the table is badly structured - there should be a row for each domain. That is going to make a solution very difficult for a beginner.

You need a piece of sql that will create a row for each domain stored in the ; separated list.

so dublin.ie;montreal.com;paris.com;bristol.uk

becomes:-

dublin.ie
montreal.com
paris.com 
bristol.uk

and then bind the rows that don't have .com back into a single row, to get

dublin.ie; bristol.uk

I had a similar issue - I has a field that contained comma separated country codes. The report user I was working for wanted country names. I had a COUNTRY table that contained Country Code and Country Name, but the comma separated list wouldn't join to it, as it has "GB,FR,IT" where the country table has "GB" for "Great Britain" and so on.

I solved this by creating a row (in memory) for each of the comma separated values, joining these rows to the COUNTRY table, returning the names of the countries, and then bound the country name back into a single line, like this:-

"Great Britain, France, Italy".

Here's the code I used - it's not for beginners, though. You might want to try using REPLACE and INSTR and SUBSTR, but you'll need to know in advance how many entries each field contains, otherwise you'll probably need to work within loops, using PLSQL.

If i were you, I'd ask the DBA (if it is possible) to structure the data properly so that the fields contain atomic values, not arrays of values.

SELECT ROW_SPLIT.CONTRACT_ID,
    WM_CONCAT(ROW_SPLIT.COUNTRY_NAME) AS COUNTRY_LIST
  FROM
    (SELECT PQ.ID AS CONTRACT_ID,
      PQ.COUNTRY_CODE,
      COUT.TEXT AS COUNTRY_NAME
    FROM
      (SELECT ID,
        extract(value(d), '//row/text()').getStringVal() AS COUNTRY_CODE
      FROM
        (SELECT ID,
          XMLTYPE('<rows><row>'
          || REPLACE(EXCLUDED_NATIONALITIES, ',', '</row><row>')
          || '</row></rows>') AS xmlval
        FROM PROPERTY_CONTRACT
        ) x,
        TABLE(xmlsequence(extract(x.xmlval, '/rows/row'))) d
      ) PQ
    JOIN COUNTRY C
    ON C.CODE = PQ.COUNTRY_CODE
    LEFT OUTER JOIN TEXT COUT
    ON C.CODE         = COUT.CODE
    AND COUT.CATEGORY = 'COUNTRY'
    AND COUT.LANGUAGE = 'en'
    ORDER BY PQ.ID,
      COUT.TEXT
    ) ROW_SPLIT
  GROUP BY ROW_SPLIT.CONTRACT_ID;

Thanks Steve, I know the database that is in use is not ideal. Another table to store these would be the best solution. unfortunately we are using a system in conjunction with the database called IQMS which cannot support this for whatever reason (so the DBA says anyway). So what i think i can logiacally do is to 1. Separate the string by ';' 2. store these temporarily in memory 3. DELETE anything with .com in that row 4. concatate the remaining rows into a string and place it back into the original row. Do you think that this is a sound way to go about the task? Thanks again! Appreciate it! — Damien Moran, Sep 13 '12 at 14:07
Yes, it's be better to restructure the data in memort, delete rows and then splice them back into a string and update the original row. Good luck! — Steve, Sep 14 '12 at 13:45

SQL: split string using a separator value

1 Answers1