Home » SQL & PL/SQL » SQL & PL/SQL » Extracting variable length string
Extracting variable length string [message #279656] Fri, 09 November 2007 09:13 Go to next message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
Hi all, I am trying to extract a variable length string. Unfortunately, I do not have access to the regexp functions.

Example 1:
StringIN (MyField): "&11213&11207&CONV00001234&1"
String I want out: CONV00001234

This is variable length, and the &1 at the end can be &1 through &9. The first 13 characters never change.

Example 2:
StringIN: "&11213&11207&P1396&1"
String I want out: P1396

I can get to this point... CONV00001234&1 or P1396&1 with this query:

select (UPPER(SUBSTR(MyField, 14, 12)) from MyTable

But I can't get rid of those pesky last two characters.

Any help is appreciated!

Brian
Re: Extracting variable length string [message #279663 is a reply to message #279656] Fri, 09 November 2007 09:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select substr('&11213&11207&CONV00001234&1',14,length('&11213&11207&CONV00001234&1')-15) from dual;

Regards
Michel
Re: Extracting variable length string [message #279664 is a reply to message #279656] Fri, 09 November 2007 09:32 Go to previous messageGo to next message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
I think this might work...

select SUBSTR(MyField, 14, LENGTH(SUBSTR(MyField,14,12))-2) from MyTable

Other solutions would be most appreciated!

Brian

[Updated on: Fri, 09 November 2007 09:34]

Report message to a moderator

Re: Extracting variable length string [message #279665 is a reply to message #279664] Fri, 09 November 2007 09:33 Go to previous messageGo to next message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
Thank you, I posted right after you, I'll try your solution too!
Re: Extracting variable length string [message #279727 is a reply to message #279656] Fri, 09 November 2007 15:36 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Also try

select substr(myfield,14,instr(myfield,'&',14,1)-14)
from mytable;

Re: Extracting variable length string [message #279728 is a reply to message #279727] Fri, 09 November 2007 15:44 Go to previous message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
Both of your solutions work great, and better than mine did! Thanks again!

Brian
Previous Topic: Stick the contents of a cursor into a temp table
Next Topic: PL/SQL Recursion??
Goto Forum:
  


Current Time: Sat Dec 03 13:42:53 CST 2016

Total time taken to generate the page: 0.16319 seconds