PL/SQL splitting a hyphenated string into new columns question.

[Datura]

Hi,

I have a column with a hyphenated string
eg: 10001-brown-030
How can I split this into three columns
eg: Size column that will contain the 10001
Colour column that will contain the brown
and amount column that will contain the 030

Cheers,
Dat

 

[Messugga]

You could use regex or the substring function. It won't be super fast on very large datasets, but it'll get the job done.

 

[Datura]

Found this:

SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one,
SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_two
FROM YOUR_TABLE t

That is for an entry that is to be separated with spaces and has only two parts. What would I need to change in order for it to have three parts split by hyphens as per my OP.

 

[Datura]

FTFY

And for three columns?

 

[dabean]

Sorry I didn't read the OP, you caught my ninja delete. Regex looks like the way to go, don't have a Oracle box to test on though.

 

[Messugga]

Greek to me I am afraid.

Okay, so I don't use Oracle very often so the regex stuff I'm a bit rusty with. You can use the REGEXP_SUBSTR function though. Google on how to use it with your specific version of Oracle.

For the substring stuff, look at this: http://www.techonthenet.com/oracle/functions/instr.php

You can go and do this:

SELECT INSTR('10001-brown-030', '-', 1, 1), which will return the index of the first occurrence of "-". Use this in combination with this: http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2101.htm

So in psuedo-code:
Select a substring from the text, starting at index 0, until the first "-"
Select a substring from the text, starting at the first "-" until the second "-"
Select a substring from the text, starting at the second "-" until the end

 

[Datura]

Cool, thanks. Useful stuff. I will play around with that

 

[dabean]

Or try this:

SELECT REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 1) col_one,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 2) col_two,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 3) col_three,
FROM YOUR_TABLE t;

Can't test it though sorry.

 

[Datura]

Or try this:

SELECT REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 1) col_one,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 2) col_two,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 3) col_three,
FROM YOUR_TABLE t;

Can't test it though sorry.

Will play with it.

 

[dadecoza]

Or try this:

SELECT REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 1) col_one,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 2) col_two,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 3) col_three,
FROM YOUR_TABLE t;

Can't test it though sorry.

^ cool solution ...

select
  substr('10001-brown-030',0,instr('10001-brown-030','-',1)-1) a,
  substr('10001-brown-030',instr('10001-brown-030','-',1)+1,instr('10001-brown-030','-',2)-1) b,
  substr('10001-brown-030',instr('10001-brown-030','-',-1)+1,length('10001-brown-030')-1) c
from dual;

nasty but works :erm:

 

[Datura]

Or try this:

SELECT REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 1) col_one,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 2) col_two,
REGEXP_SUBSTR(t.column_one, '[^-]+', 1, 3) col_three,
FROM YOUR_TABLE t;

Can't test it though sorry.

Gives me 'Missing expression' error.

 

[dabean]

Gives me 'Missing expression' error.

Try escaping the - with a backslash: '[^\-]+'

 

[grok]

Say your original column name is called 'column1' then this should work

select	substr( column1, 1, instr( column1, '-', 1, 1 ) - 1 ) "size" 
,	substr( column1, instr( column1, '-', 1, 1 ) + 1, instr( column1, '-', 1, 2 ) - instr( column1, '-', 1, 1 ) - 1) "color"
,	substr( column1, instr( column1, '-', 1, 2 ) + 1, length( column1 ) - instr( column1, '-', 1, 2 )) "amount"
from	your_table ;

Dade beat me to it :-/

 

[Messugga]

^ cool solution ...

select
  substr('10001-brown-030',0,instr('10001-brown-030','-')-1) a,
  substr('10001-brown-030',instr('10001-brown-030','-')+1,instr('10001-brown-030','-')-1) b,
  substr('10001-brown-030',instr('10001-brown-030','-',-1)+1,length('10001-brown-030')-1) c
from dual;

nasty but works :erm:

Nasty indeed, but since SQL Server doesn't exactly support Regex, it's a valid option. As I said in my original post, Regex is probably the way to go, but as I haven't used it on an Oracle environment in a while and you get different standards and blahblahblah, I didn't feel like that would be a useful contribution from my side.

 

[Datura]

Try escaping the - with a backslash: '[^\-]+'

Still throwing missing expression error.

 

[yazzo]

Too many commas.

 

[Datura]

Too many commas.

Yep

Thanks