Home » SQL & PL/SQL » SQL & PL/SQL » extracting strings (sql oracle xp)
extracting strings [message #353434] Mon, 13 October 2008 22:10 Go to next message
lumpsum
Messages: 4
Registered: October 2008
Location: Santa Monica
Junior Member
hey,

i need to extract a string that is within a string but using only the functions rtrim, substr, instr and lengt

the strings look something like "12 xxxxx blah" where i need to extract the xxxxx

not sure why this doesnt work:

SELECT SUBSTR(string_name,INSTR(string_name,' ',1,1),INSTR(string_name,' ',1,2)) FROM table; 


but for some reason it returns "xxxxx bl". any help appreciated
Re: extracting strings [message #353441 is a reply to message #353434] Mon, 13 October 2008 22:35 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
The last parameter is the length...how namy character u wanna extract.
Re: extracting strings [message #353443 is a reply to message #353434] Mon, 13 October 2008 22:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I prefer you to go through STRING FUNCTIONS in detail.

In your case 3rd parameter is creating the problem . Go through the link and Fix it.

Thumbs Up
Rajuvan.
Re: extracting strings [message #353446 is a reply to message #353434] Mon, 13 October 2008 22:42 Go to previous messageGo to next message
lumpsum
Messages: 4
Registered: October 2008
Location: Santa Monica
Junior Member
thanks for the link, reading it right now

tanmoy1048 , the number of characters to extract is variable, it changes between each row so using those spaces is the key
Re: extracting strings [message #353449 is a reply to message #353434] Mon, 13 October 2008 22:54 Go to previous messageGo to next message
lumpsum
Messages: 4
Registered: October 2008
Location: Santa Monica
Junior Member
maybe im tired but i feel ive been through everything in that websites suggestions

if i use length instead of that second instr(string_name,' ',1,2), i just end up getting the rest of the whole string after the space "xxxxx blahblah"

im close but very stuck
Re: extracting strings [message #353452 is a reply to message #353434] Mon, 13 October 2008 23:06 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
SELECT SUBSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),
1,INSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),' ',1,1)) FROM dual;


It may have some simple form .... i am not sure.
Re: extracting strings [message #353457 is a reply to message #353434] Mon, 13 October 2008 23:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

tanmoy1048,

Let OP search and find.

Anyway , your query is not exactly same as Op Wants.

Check

SELECT LENGTH(SUBSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),
1,INSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),' ',1,1))) x ,
Length('xxxxx') y FROM dual;



@ lumpsum ,
You can search another method

Thumbs Up
Rajuvan.


Re: extracting strings [message #353468 is a reply to message #353434] Tue, 14 October 2008 00:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
this is actually a trivial piece of work. Your problem is not in understanding both substr and instr. Please go back and read the manual or do a quick google to get the syntax of each. Once you read up on both of these again, you will see that you are not supplying the right values to them.

When I have problems with complex substr(instr(... expressions, I figure it out by breaking down the process into its small parts.

In your case that means this:

SELECT SUBSTR(string_name,INSTR(string_name,' ',1,1),INSTR(string_name,' ',1,2)) FROM table; 

becomes this

select INSTR(string_name,' ',1,1)
      ,INSTR(string_name,' ',1,2)
from table
/

select SUBSTR(string_name,??1,??2)
from table
/
where ??1,??2 are the results from the previous query.

Give breaking down your query a try. You will say "DOH!" what a dummy I have been. But we all do it.

Good luck, Kevin
Re: extracting strings [message #353470 is a reply to message #353452] Tue, 14 October 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@tanmoy1048

Once again don't post solution just clue, hint or link.
FOLLOW THE GUIDELINES.

Regards
Michel
Re: extracting strings [message #353484 is a reply to message #353457] Tue, 14 October 2008 00:50 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
rajavu1 wrote on Mon, 13 October 2008 23:20
tanmoy1048,

Let OP search and find.

Anyway , your query is not exactly same as Op Wants.

Check

SELECT LENGTH(SUBSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),
1,INSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),' ',1,1))) x ,
Length('xxxxx') y FROM dual;



@ lumpsum ,
You can search another method

Thumbs Up
Rajuvan.




Dear rajuvan... would you please explain the problem of given solution ?
thanx.
Re: extracting strings [message #353493 is a reply to message #353434] Tue, 14 October 2008 00:59 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



It is not a bad solution at all .

SQL> SELECT LENGTH(SUBSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),
1,INSTR(SUBSTR('12 xxxxx blah',INSTR('12 xxxxx blah',' ',1,1)+1),' ',1,1))) x ,
Length('xxxxx') y FROM dual;  2    3

         X          Y
---------- ----------
         6          5

SQL>


OP wants to get the output as 'xxxxx' But your query gives 'xxxxx ' ( extra space) Thats all .

Thumbs Up
Rajuvan.
Re: extracting strings [message #353714 is a reply to message #353434] Tue, 14 October 2008 16:40 Go to previous messageGo to next message
lumpsum
Messages: 4
Registered: October 2008
Location: Santa Monica
Junior Member
hey everyone,

i know the problem was the length value of my substr, it was always a few characters off since i trimmed off the characters at the start of my string.

this works by subtracting the number i trimmed off:

SELECT SUBSTR(string_name,(INSTR(string_name,' ')),
(INSTR(string_name,' ',1,2)-(INSTR(string_name,' ')))) 
FROM table;


i know there is a better way to do this of course but i couldnt get my head around the length problem in my substr

12 xxxxx blahblah
12345678901234567 <==characters position

my substr would go from position 3, with length 9 so thats why i would get the few extra characters as the substrings starting position was now altered

[Updated on: Wed, 15 October 2008 00:36] by Moderator

Report message to a moderator

Re: extracting strings [message #353736 is a reply to message #353714] Tue, 14 October 2008 23:08 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
In the begining I had a lot of trouble with the substr and instr functions too. I eventually started doing some simple math and it all worked out for me in the end. Try this ugly but useful post on for size:

4--- 3-- 2-
abcd xyz pq
12345678901
         1

It is easy to see the breakouts of a string when one plots the text against actual number lines. Old fashioned but effective.

instr(s,d,p,n) means: search string S, start your search in position P, and find occurrance N of delimiter D

instr('abcd xyz pq',' ',1,1)  means: search 'ABCD XYZ PQ', start at position 1, and find occurrance 1 of a space
instr('abcd xyz pq',' ',1,2)  means: search 'ABCD XYZ PQ', start at position 1, and find occurrance 2 of a space


SQL> select instr('abcd xyz pq',' ',1,1),instr('abcd xyz pq',' ',1,2) from dual;

INSTR('ABCDXYZPQ','',1,1) INSTR('ABCDXYZPQ','',1,2)
------------------------- -------------------------
                        5                         9

1 row selected.

SQL> 

the first space is in position 5; thus 5=instr('abcd xyz pq',' ',1,1)
the second space is in position 9; thus 9=instr('abcd xyz pq',' ',1,2)


HMM... Lets us use our knowledge of 5 and 9!

substr(S,P,L) means: chop up string S, start in position P and take L characters

From our number linen above we know that xyz stars in position 6 and goes for 3 characters.
SQL> select substr('abcd xyz pq',6,3) from dual;

SUB
---
xyz

1 row selected.

SQL> 

Using what we learned above about the values of 5 and 9 we state the following unusual but true facts: that 6=5+1 and that 3=9-5-1. We also above figured out formulas for computing 5 and 9 so lets plug them in to our substring expression:

thus 'xyz'=substr('abcd xyz pq',instr('abcd xyz pq',' ',1,1)+1,instr('abcd xyz pq',' ',1,2)-instr('abcd xyz pq',' ',1,1)-1)
thus 'xyz'=substr('abcd xyz pq',5                           +1,9                           -5                           -1)
thus 'xyz'=substr('abcd xyz pq',6                             ,3                                                          )


or
     'xyz'=substr('abcd xyz pq',6,3)  start in position 6, take 3 characters


By using number lines positioned against our string we can see the makeup of the string. By using a simple reduction technique, we can see how code is constructed and deconstructed for string operations in SQL.

It will take you 20 minutes to work through all these numbers. When you get lost, go back to the top where the number line is and remind yourself of the position of each space (and thus the start of each substring) in your string. Keep at it till the light turns on. Once you get it you will use it over and over.

We can assuming our delimiters are one character long, boil substring extraction down to one generic expression:

??? = substr(S,instr(S,D,1,N)+1,instr(S,D,1,N+1)-instr(S,D,1,N)-1)

Good luck, Kevin

[Updated on: Tue, 14 October 2008 23:17]

Report message to a moderator

Previous Topic: confusions about the query
Next Topic: trying to find out table name based on a value
Goto Forum:
  


Current Time: Wed Dec 07 11:03:53 CST 2016

Total time taken to generate the page: 0.06363 seconds