Home » SQL & PL/SQL » SQL & PL/SQL » Library function
| Library function [message #20311] |
Mon, 13 May 2002 08:56  |
Mark the Shark
Messages: 15
Registered: February 2002
|
Junior Member |
|
|
Hello, I want to know how to caputure the second word only i.e.
Ghost from the Grand Banks results=from
Prints of the 20th Century results=of
Vixen 07 results = 07
Can I use a trailing space (btitle ||' '), ?
Thanks
|
|
|
|
|
|
| Re: Library function [message #20314 is a reply to message #20311] |
Mon, 13 May 2002 09:38  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Mark, here's one way. If you are doing this in a select, you could use decode to shorten up the expression a bit, but this works using NVL:
declare
s varchar2(255);
begin
s := 'Ghost from the Grand Bank';
dbms_output.put_line( 'String: ' || s || ' / Second word: ' || nvl(substr(s, instr(s, ' ') + 1, instr(s, ' ', 1, 2) - instr(s, ' ') - 1), substr(s, instr(s, ' ') + 1)) );
s := 'Prints of the 20th Century';
dbms_output.put_line( 'String: ' || s || ' / Second word: ' || nvl(substr(s, instr(s, ' ') + 1, instr(s, ' ', 1, 2) - instr(s, ' ') - 1), substr(s, instr(s, ' ') + 1)) );
s := 'Vixen 07';
dbms_output.put_line( 'String: ' || s || ' / Second word: ' || nvl(substr(s, instr(s, ' ') + 1, instr(s, ' ', 1, 2) - instr(s, ' ') - 1), substr(s, instr(s, ' ') + 1)) );
end;
String: Ghost from the Grand Bank / Second word: from
String: Prints of the 20th Century / Second word: of
String: Vixen 07 / Second word: 07
There is an extra bit of code to handle the 'Vixen 07' case (which doesn't have a second space to use).
|
|
|
|
|
|
| Re: Library function [message #20321 is a reply to message #20314] |
Mon, 13 May 2002 10:59 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Just put the expression in a select statement - it's not like you have to totally disregard my response.
select nvl(substr(title, instr(title, ' ') + 1, instr(title, ' ', 1, 2) - instr(title, ' ') - 1), substr(title, instr(title, ' ') + 1))
from table |
|
|
|
| Re: Library function [message #20323 is a reply to message #20316] |
Mon, 13 May 2002 11:02 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Unlimate? I suppose you mean eliminate.
select substr(title, 1, instr(title, ' ')) || substr(title, instr(title, ' ', 1, 2) + 1) no_second_word
from table |
|
|
|
Goto Forum:
Current Time: Thu Apr 25 19:58:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
