Home » SQL & PL/SQL » SQL & PL/SQL » How to break a string
How to break a string [message #216704] Tue, 30 January 2007 01:24 Go to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi,

i have to break a string "ad_df_DF_dg_qw"
and insert each value to differnt columns in a table (like ad,df,DF,dg,qw).

Thanks in advance
Hari Krishna
Re: How to break a string [message #216707 is a reply to message #216704] Tue, 30 January 2007 01:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Now this is fun.

You could use something like string_to_tab for that.

SQL> col the_value format a10
SQL> select the_value
  2  from table(string_to_tab('ad_df_DF_dg_qw','_'))
  3  /

THE_VALUE
----------
ad
df
DF
dg
qw


Edit: Don't forget to check out the pure SQL solution in the same thread.

MHE

[Updated on: Tue, 30 January 2007 01:32]

Report message to a moderator

Re: How to break a string [message #216709 is a reply to message #216707] Tue, 30 January 2007 01:39 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Thanks Maaher.

rgds
Hari Krishna
Re: How to break a string [message #216763 is a reply to message #216704] Tue, 30 January 2007 05:18 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
In addition to Maaher's solution, if you are on 10g, you can consider:
SQL> select regexp_substr(str, '[^_]+', 1, level) Letters
  2     from (select 'ad_df_DF_dg_qw' str from dual)
  3    connect by regexp_substr(str, '[^_]+', 1, level) is not null;

LETTERS
--------------------
ad
df
DF
dg
qw

Else,
SQL> select trim(substr(str, instr(str, '_', 1, rownum) + 1,
  2	instr(str, '_', 1, rownum + 1) - instr(str, '_', 1, rownum) -1)) Letters
  3	from (select '_'||'ad_df_DF_dg_qw'||'_' str from dual)
  4  connect by level < length(str) - length(replace(str, '_'));

LETTERS
--------------------
ad
df
DF
dg
qw
Re: How to break a string [message #216765 is a reply to message #216763] Tue, 30 January 2007 05:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Wow, ebrian is the regular expression expert! Beautiful!

MHE
Re: How to break a string [message #216767 is a reply to message #216765] Tue, 30 January 2007 05:35 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks Maaher ! Regex is definitely a big benefit with 10g.
Re: How to break a string [message #216826 is a reply to message #216704] Tue, 30 January 2007 10:40 Go to previous messageGo to next message
sidagam
Messages: 24
Registered: February 2005
Location: Hyderabad
Junior Member
Thanks eBrain for the 2nd solution you given...It will fit for even 9i also....

Thanks & Regards,

Babu SRSB.
Re: How to break a string [message #216979 is a reply to message #216826] Wed, 31 January 2007 02:41 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

hi ebrian,

Sorry for late reply. Iam using oracle 9i. Your 2nd solution is working fine for me.

Rgds
Hari Krishna
Re: How to break a string [message #217019 is a reply to message #216979] Wed, 31 January 2007 06:09 Go to previous message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi Maaher

Using Your string_to_tab Function iam able to break the string.
i have a table SAMPLE with 4 columns like col1,col2,col3,col4.

if i execute the command

SQL> select the_value
from table(string_to_tab('ad_df_DF_dg','_'));

i will get the values ad,df,DF and dg.

now i want to insert these values into the table "sample"
i.e


col1 col2 col3 col4
===== ==== ====== ======
ad df DF dg


thanks in advance

rgds
Hari Krishna
Previous Topic: How to include single quotes as character?
Next Topic: Accessing oracle 10g xe from a client
Goto Forum:
  


Current Time: Sun Dec 04 04:40:57 CST 2016

Total time taken to generate the page: 0.09463 seconds