By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

get the right side of string ?

P: n/a
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)

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Language SQL, in a stored proc.

"Bill Smith" <x@x.com> 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)

Nov 12 '05 #2

P: n/a
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!

Nov 12 '05 #3

P: n/a
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!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.