|
View previous topic :: View next topic
|
| Author |
Message |
jeeva84
New User
Joined: 18 Apr 2008
Posts: 7
Location: India
|
|
|
|
Eg
How will you find the position of second occurance of letter 'T'
in the string 'tom has a toy" |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| using what ???? |
|
| Back to top |
|
|
jeeva84
New User
Joined: 18 Apr 2008
Posts: 7
Location: India
|
|
|
|
| Using any DB2 option like LOCATE etc. I want to get that in a query |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Jeeva,
LOCATE and POSSTR will give you only the first occurence ... you may have to fetch and use INSPECT on the col to get the 2nd occurence .. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| ashimer wrote: |
Jeeva,
LOCATE and POSSTR will give you only the first occurence ... you may have to fetch and use INSPECT on the col to get the 2nd occurence .. |
| Code: |
SELECT * FROM NAMES WHERE POSSTR(LAST_NAME, 'T') <> 0
AND LOCATE('T', LAST_NAME, POSSTR(LAST_NAME, 'T') + 1) <> 0;
|
This works but may cause an error if the first 'T' is in the last position of the LAST_NAME column (I didn't have a quick way to check that).
You could try adding
| Code: |
| AND POSSTR(LAST_NAME, 'T') + 1 <= LENGTH(LAST_NAME) |
|
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Building upon Craq's suggestion use this query to get the 2nd occurence of T ...
| Code: |
SELECT LOCATE('T',NAME,LOCATE('T',NAME)+1)
FROM TABLE WHERE NAME = 'TOM HAS A TOY';
|
Thanks Craq for the idea ... I should have tried a lil research on LOCATE function ... |
|
| Back to top |
|
|
jeeva84
New User
Joined: 18 Apr 2008
Posts: 7
Location: India
|
|
|
|
| Thanks Ashimer and Craq |
|
| Back to top |
|
|
|
|
