SQLTeam.com | Weblogs | Forums

Split Multiple Column Values into Multiple Rows MySQL


#1

Hi im having difficulties in splitting multiple column values into multiple rows, i have already done this code in oracle so here is the source table and desired output

SOURCE:
DEAL_ID | GROUP_ID
1 | 12 13 14
2 | 15 16
3 | 17

OUTPUT:
DEAL_ID | GROUP_ID
1 | 12
1 | 13
1 | 14
2 | 15
2 | 16
3 | 17

I have already did this in oracle sql, the code goes like this.

CREATE table mydb.DEAL_GROUPING
SELECT DISTINCT
DEAL_ID,
regexp_substr(GROUP_ID,'[^" "]+', 1, level) as GROUP_ID_NEW
FROM DEAL_UPLOAD
CONNECT BY regexp_substr(GROUP_ID, '[^" "]+', 1, level) is not null

the problem here is, this regexp_substr function does not exist/valid in MySQL. can any body help me translate this function from oracle to mysql?

Thnks,
Nasman


#2

See: SQL Splitter


#3

Not sure which platform you are needing to get this working on, but this forum is for Microsoft SQL Server so you might find that folk here don't know the answer that you need for Oracle/MySQL - although they'll definitely be able to give you an answer if you want an MS SQL Server solution :smile:


#4

Hi, to clarify things, the code is working in oracle environment, i need a similar script for mysql. :smile:

Cheers


#5

You'll probably get better help on a MySql forum