| |
|
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.
|
 |
|
03-24-10, 06:04
|
|
Registered User
|
|
Join Date: Aug 2009
Posts: 15
|
|
|
db2 substr locate problem
|
|
hi everyone ,
i got stuck in a problem
here it is
one of the db2 table is storing a String
like this :
world/asia/india/100
world/asia/china/101
world/europe/argentina/10555
like this
no of data is stored here
here i want to use select statament
to get the output like this
100
101
10555
only
i have used substr +locate function but i am not getting the proper output
can anyone help me out
thanks for the help
|
|
03-24-10, 07:20
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
can you post what you tried
a tip: before parsing append "/" to the input string ...
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-24-10, 07:44
|
|
Registered User
|
|
Join Date: Aug 2009
Posts: 15
|
|
|
select last no from select statment using rtrim
|
|
yeah sure
here it is
select Substr(Substr(Substr(country_no,(LOCATE('/',country_no,1)+1)),(LOCATE('/',Substr(country_no,(LOCATE('/',country_no,1)+1)),1)+1)),(LOCATE('/',Substr(Substr(country_no,(LOCATE('/',country,1)+1)),(LOCATE('/',Substr(country_no,(LOCATE('/',country_no,1)+1)),1)+1)),1)+1)) from world_master fetch first 10 rows only
this command is working
but this is the long way to do
i m trying to do with rtrim
please help me out
i think that will be a short command
thank fro the reply
|
|
03-24-10, 09:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
Substrs other than outmost are not necessary, bacause LOCATE can use third parameter to specify starting position of search.
You used one("1") as third parameter for all LOCATE functions.
Here is a shorter example.
Code:
------------------------------ Commands Entered ------------------------------
WITH
world_master(country_no) AS (
VALUES
'world/asia/india/100'
, 'world/asia/china/101'
, 'world/europe/argentina/10555'
)
SELECT country_no
, SUBSTR(country_no , LOCATE('/', country_no, LOCATE('/', country_no, LOCATE('/', country_no) + 1) + 1) + 1 )
FROM world_master
;
------------------------------------------------------------------------------
COUNTRY_NO 2
---------------------------- ----------------------------
world/asia/india/100 100
world/asia/china/101 101
world/europe/argentina/10555 10555
3 record(s) selected.
|
|
03-24-10, 09:38
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
if you are in 9.7, try
select substr(country_no,locate_in_string(country_no,'/',1,3)) from world_master
well, haven't tested it myself .. so, expect syntax errors ;-)
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-24-10, 10:16
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
From manual "IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1"
Quote:
|
INSTR can be used as a synonym for LOCATE_IN_STRING.
|
|
|
03-24-10, 10:18
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
select substr(country_no,locate_in_string(country_no,'/',1,3)+1) from world_master
|
|
03-24-10, 14:09
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
Quote:
Originally Posted by tonkuma
select substr(country_no,locate_in_string(country_no,'/',1,3)+1) from world_master
|
Cheers Tonkuma!
i knew I wouldn't get it right ;-)
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-24-10, 14:17
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
What if there are more than three /'s? I know that's what was given in the example, but what about accounting for provinces/states? Would it be a better solution in the long run to find the '/numeric'?
Dave
|
|
03-24-10, 15:59
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,534
|
|
Dave, I agree with you to an extent ..
I guess it will be best to put this in a UDF so that any future changes can be easily done.
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
03-24-10, 16:22
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
Using LOCATE_IN_STRING...
Code:
------------------------------ Commands Entered ------------------------------
WITH
world_master(country_no) AS (
VALUES
'world/asia/india/100'
, 'world/asia/china/101'
, 'world/europe/argentina/10555'
, 'world/europe/eu/france/702646'
, 'world/antarctic/50'
, 'moon/2030'
)
SELECT country_no
, SUBSTR( country_no
, LOCATE_IN_STRING( country_no
, '/'
, 1
, LENGTH(country_no)
- LENGTH( REPLACE(country_no, '/', '') )
) + 1
) next_to_last_slash
FROM world_master;
------------------------------------------------------------------------------
COUNTRY_NO NEXT_TO_LAST_SLASH
----------------------------- -----------------------------
world/asia/india/100 100
world/asia/china/101 101
world/europe/argentina/10555 10555
world/europe/eu/france/702646 702646
world/antarctic/50 50
moon/2030 2030
6 record(s) selected.
|
|
03-24-10, 16:29
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
This will be better.
Code:
SELECT country_no
, SUBSTR( country_no
, LOCATE_IN_STRING( country_no
, '/'
, -1
) + 1
) next_to_last_slash
FROM world_master;
|
|
03-24-10, 16:33
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,194
|
|
"IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1"
Quote:
LOCATE_IN_STRING
...
start
...
...
If the value of the integer is less than zero, the search begins at LENGTH(source-string) + start + 1
and continues for each position to the beginning of the string.
|
|
|
03-24-10, 18:04
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Translate + strip
Quote:
Originally Posted by ashrash
hi everyone ,
i got stuck in a problem
here it is
one of the db2 table is storing a String
like this :
world/asia/india/100
world/asia/china/101
world/europe/argentina/10555
like this
no of data is stored here
here i want to use select statament
to get the output like this
100
101
10555
only
i have used substr +locate function but i am not getting the proper output
can anyone help me out
thanks for the help
|
You can TRANSLATE all not numbers in spaces then remove spaces.
You'll get what do you need.
Lenny
|
|
03-25-10, 00:26
|
|
Registered User
|
|
Join Date: Aug 2009
Posts: 15
|
|
|
db2 substr and locate
hi guys,
thanks for the all replies and suggestions shared....
locate_in_string as well INSTR is not working in db29.5 and db2 8.2 version so i cannnot use this
what i m thinking is to read the string from last and seacrh for the first '/' slash
if found then
trim from there and display
because there are possiblites of more than 3 '/' slashes ....
but i dont know how to write the exaxct command .well i m trying myself ...
if you guys can help me out finding the exaxt select statment pls do
thanks
|
|
| 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
>
db2 substr locate problem
