Home » SQL & PL/SQL » SQL & PL/SQL » Prasing a text field (merged)
Prasing a text field (merged) [message #382281] Wed, 21 January 2009 14:12 Go to next message
sasrs991984
Messages: 3
Registered: June 2008
Junior Member
I have a table with a description field that contains some data such as you see here. Is there a function I can use to strip out the division codes that appear after each of the "D" characters (3400, 3300, 3600, 3800, 3900, and 4200 in this example) from the following string?

BF LOIN NY STYLE SIRLOIN D3400-S9-I1347,D3300-S9-I1347,D3600-S9-I1347,D3800-S9-I1347,D3900-S9-I1347,D4200-S9-I1347
Re: Prasing a text field (merged) [message #382283 is a reply to message #382281] Wed, 21 January 2009 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use regular expression function and don't multipost your question.

Regards
Michel
Re: Prasing a text field (merged) [message #382288 is a reply to message #382283] Wed, 21 January 2009 14:53 Go to previous messageGo to next message
sasrs991984
Messages: 3
Registered: June 2008
Junior Member
Sorry about my multi-posting. My browser seemed to be stuck. As far as the regex sucggestion, I don't think 9i supports regex. Please correct me if I am wrong.
Re: Prasing a text field (merged) [message #382297 is a reply to message #382288] Wed, 21 January 2009 16:57 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Play with this a bit. Tested on 11g, but should work on 9.2.
from asktom.com

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

SQL> variable txt varchar2(1000);
SQL>
SQL> exec :txt := 'BF LOIN NY STYLE SIRLOIN D3400-S9-I1347,D3300-S9-I1347,D3600-S9-I1347,D3800-S9-I1347,D3900-S9-I1347,D4200-S9-I134
7';

PL/SQL procedure successfully completed.

SQL>
SQL> set linesize 90
SQL> column token format a40
SQL> column val format a40
SQL>
SQL> with data as(
  2  select
  3  trim( substr (txt,
  4  instr (txt, ',', 1, level ) + 1,
  5  instr (txt, ',', 1, level+1)- instr (txt, ',', 1, level) -1 ) ) as token
  6   from (select ','||:txt||',' txt
  7   from dual)
  8   connect by level <=
  9   length(:txt)-length(replace(:txt,',',''))+1 )
 10  select token, substr(token, 2, instr(token, '-')-2) val from data;

TOKEN                                    VAL
---------------------------------------- ----------------------------------------
BF LOIN NY STYLE SIRLOIN D3400-S9-I1347  F LOIN NY STYLE SIRLOIN D3400
D3300-S9-I1347                           3300
D3600-S9-I1347                           3600
D3800-S9-I1347                           3800
D3900-S9-I1347                           3900
D4200-S9-I1347                           4200

6 rows selected.

SQL>SQL> exec :txt := replace('BF LOIN NY STYLE SIRLOIN D3400-S9-I1347,D3300-S9-I1347,D3600-S9-I1347,D3800-S9-I1347,D3900-S9-I1347,D4200-S9-I1347', ' ', ',');

PL/SQL procedure successfully completed.

SQL> with data as(
  2  select
  3  trim( substr (txt,
  4  instr (txt, ',', 1, level ) + 1,
  5  instr (txt, ',', 1, level+1)- instr (txt, ',', 1, level) -1 ) ) as token
  6   from (select ','||:txt||',' txt
  7   from dual)
  8   connect by level <=
  9   length(:txt)-length(replace(:txt,',',''))+1 )
 10  select X.val from (select token, substr(token, 2, instr(token, '-')-2) val from data)X
 11   where X.val is not null;

VAL
----------------------------------------
3400
3300
3600
3800
3900
4200

6 rows selected.

SQL>
Re: Prasing a text field (merged) [message #382320 is a reply to message #382288] Wed, 21 January 2009 23:08 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sasrs991984 wrote on Wed, 21 January 2009 21:53
Sorry about my multi-posting. My browser seemed to be stuck. As far as the regex sucggestion, I don't think 9i supports regex. Please correct me if I am wrong.

How could we know you are using 9i?
Read OraFAQ Forum Guide and follow what should be posted in question like Oracle version (with 4 decimals).

Regards
Michel

Previous Topic: How to select the MAX(PK_ID) value in a procedure
Next Topic: Simple union in oracle 10g?
Goto Forum:
  


Current Time: Sat Dec 10 07:11:28 CST 2016

Total time taken to generate the page: 0.08582 seconds