| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
05-11-10, 06:26
|
|
Registered User
|
|
Join Date: May 2010
Posts: 5
|
|
|
Using locate with right function
|
|
How can i locate the position of the last underscore in the string 'file_name_may'. Is will be using right and locate function. But could anyone let me know the syntax.
I need to extract the characters after the last underscore. For eg. in this string may
|
|
05-11-10, 07:43
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
05-11-10, 11:47
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
DB2 for beginners
|
|
It is much easier, is not ?
Code:
with posn(psn) as
(select 1 from sysibm.sysdummy1
union all
select psn + 1
from posn where psn <= 254
)
select max(psn) last_location_of_searching_smb
from posn
join (select 'file_name_may' str from sysibm.sysdummy1) s
On substr(str, psn, 1) = '_'
Result:
Quote:
LAST_LOCATION_OF_SEARCHING_SMB
10
|
Lenny
|
|
05-11-10, 11:49
|
|
Registered User
|
|
Join Date: May 2010
Posts: 5
|
|
Thanks a ton tonkuma...it was helpful.... 
|
|
05-11-10, 15:33
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Different point of view
Solution could be different:
Code:
with source (str) as
(select 'file_name_may '
from sysibm.sysdummy1
)
,
searching_str(srch) as
(select '_'
from sysibm.sysdummy1
)
,
locate_all_ss (str, srch, ssposn) as
(select str, srch, locate(srch, str, 1)
from source, searching_str
union all
select str, srch, locate(srch, str, ssposn + length(srch))
from locate_all_ss
where locate(srch, str, ssposn + length(srch) ) > 0
and length(srch) > 0
)
,
locate_last_ss_posn (str, srch, last_ss_posn) as
(
select str, srch, max(ssposn)
from locate_all_ss
group by str, srch
)
select * from locate_last_ss_posn
But result stays the same:
Quote:
STR.................. SRCH.........LAST_SS_POSN
file_name_may...... _.................. 10
|
Lenny
|
|
05-11-10, 16:33
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Get result for list of strings
How you can see the last query working good with list of source and search strings:
Quote:
with source (str) as
(select 'file_name_may '
from sysibm.sysdummy1
union all
select 'search_string_not_found'
from sysibm.sysdummy1
)
,
searching_str(srch) as
(select '_'
from sysibm.sysdummy1
union all
select '_n'
from sysibm.sysdummy1
)
,
locate_all_ss (str, srch, ssposn) as
(select str, srch, locate(srch, str, 1)
from source, searching_str
union all
select str, srch, locate(srch, str, ssposn + length(srch))
from locate_all_ss
where locate(srch, str, ssposn + length(srch) ) > 0
and length(srch) > 0
)
,
locate_last_ss_posn (str, srch, last_ss_posn) as
(
select str, srch, max(ssposn)
from locate_all_ss
group by str, srch
)
select * from locate_last_ss_posn
|
Result:
Quote:
STR............................SRCH............ LAST_SS_POSN
file_name_may............... ....._...............10
file_name_may................... _n................5
search_string_not_found........_...............18
search_string_not_found......._n..............14
|
Lenny
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Using locate with right function
