SQL query to split contents by delimiter

how many records do you have like this?

AW

Depending the your DBMS it might be possible to address your problem with a suitably horrendous query. What DBMS are you using?

Here's a PostgreSQL version that works for the most part:

SELECT split_part( Expertise, ';', 1 ) AS expertise FROM tablename
UNION
SELECT split_part( Expertise, ';', 2 ) FROM tablename
UNION
SELECT split_part( Expertise, ';', 3 ) FROM tablename
UNION
....

You would have to do this as many times as there are "fields" in the Expertise column. Messy, but it works.

Hello

Let me know what DBMS you are using.

Thanks
Shabbir Bhimani

Hi,
select replace(Expertise,';',CHR(10))val from <tablename>;

The above query splits ur delimited data & displays as many rows.

eg:Apples
     Bananas
     Coconuts
U can order it from ur code.I think this will solve ur probs.

Thanx
-Murali*

I am using an MS Access database.

@MuraliKanth: your query "SELECT REPLACE(Expertise,';',CHR(10)) FROM MyTable" didn't work, got this error

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression.

What version of Access are you using?

AW

and even if you could use the Replace function as suggested, that will NOT produce the resultset that you need.  The 'split' fields will still be seen, by Access as a SINGLE value, and you cannot sort these fields.

so if, instead of

Apples;Bananas;Coconuts

which already just happens to be in alphabetical order, the field contained:

Coconuts;Apples;Bananas  

you would NOT be able to sort these values into alphabetical order.  The SINGLE field would simply contain

Coconuts
Apples
Bananas  

and the individual values would NOT exist independently, and could NOT be sorted.

AW

@pmosinskis
Pls check out the function for replace in Ms-Acess.
U can go thro the String functions avaialable in Ms-Access.

The query whichever i have given u is in Oracle.
I have mentioned tht sorting u have to do thro ur coding.Since a single field value cannot be sorted using query.

Regards
*Murali

The easiest way (assuming you only want to do this once or on relatively rare occasions) would seem to be:

1)  Export the column to a text file.
2)  Use a text editor to Search and Replace, changing all semicolons to line feeds
3)  Import the text file

If you need to do this on a regular basis VBA would seem to be the best choice.  If this is the case I can post code that would perform the operation--just let me know.