get the right side of string ?

[Bill Smith]

Anyone have a one liner of code to getting the right side of a string after
the last occurence of a given character?
Thanks, Bill

ie '*abc*def*ghi' would yield '*ghi' (or 'ghi' would be acceptable)

[Bill Smith]

Language SQL, in a stored proc.

"Bill Smith" <[e-mail]> wrote in message
news:58********************@fe2.columbus.rr.com...

Anyone have a one liner of code to getting the right side of a string
after the last occurence of a given character?
Thanks, Bill

ie '*abc*def*ghi' would yield '*ghi' (or 'ghi' would be acceptable)

[Philip Sherman]

Bill Smith wrote:

Anyone have a one liner of code to getting the right side of a string after
the last occurence of a given character?
Thanks, Bill

ie '*abc*def*ghi' would yield '*ghi' (or 'ghi' would be acceptable)

The following function will. Note that the output is limited to 50
bytes. If you need a possible longer output, you'll have to change it
accordingly. Invoke the function by passing it the arguments.

Phil Sherman
drop function posstrl!

create function posstrl (haystack varchar(4000), needle char(1))
returns varchar(50) deterministic no external action
contains sql
begin atomic
declare i1 int;
declare i2 int;
set i1 = locate(needle,haystack);
while i1 > 0 do
set i2 = i1;
set i1 = locate(needle,haystack,i1+1);
end while;
return rtrim(right(haystack,(length(haystack)-i2)));
end!

[Bill Smith]

Thanks Philip,
I was hoping to get rid of the looping. I had a similar while loop using
the RIGHT and LENGTH functions, but yours is more efficient using LOCATE.
Thanks,
Bill
"Philip Sherman" <ps******@ameritech.net> wrote in message
news:WN******************@newssvr31.news.prodigy.c om...

Bill Smith wrote:

Anyone have a one liner of code to getting the right side of a string
after the last occurence of a given character?
Thanks, Bill

ie '*abc*def*ghi' would yield '*ghi' (or 'ghi' would be acceptable)

The following function will. Note that the output is limited to 50 bytes.
If you need a possible longer output, you'll have to change it
accordingly. Invoke the function by passing it the arguments.

Phil Sherman
drop function posstrl!

create function posstrl (haystack varchar(4000), needle char(1))
returns varchar(50) deterministic no external action
contains sql
begin atomic
declare i1 int;
declare i2 int;
set i1 = locate(needle,haystack);
while i1 > 0 do
set i2 = i1;
set i1 = locate(needle,haystack,i1+1);
end while;
return rtrim(right(haystack,(length(haystack)-i2)));
end!