Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Passero
Starting Member
12 Posts |
 Posted - 2008-01-24 : 03:53:52
|
| I have a script from oracle that i have to convert to TSQL. I now have this problem with converting instr to charindexIn oracle the use is: Instr(column,findWhat,start,occurence)I need the last parameter but the charIndex does not support it. For example i have a field with this value:1|23|456|890 123|43|876|878If i want to get the index of the 3th pipe i use:instr(column,'|',1,3) so i get the correct. In Tsql i can only get the index of the first one. Is there a function that does the same or how could i resolve this problem? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-24 : 04:13:51
|
| DECLARE @V varchar(100)SET @V='1|23|456|890'SELECT LEN(@V) - CHARINDEX('|',REVERSE(@V)) +1 |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-24 : 07:31:47
|
| Visakh16's code will only work if there are 4 sections in the incoming data.....which may not be the case...what was supplied by the OP may just have been a sample, not reflecting all the data. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-24 : 07:37:19
|
Search this site for a function named fnParseString.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Passero
Starting Member
12 Posts |
Posted - 2008-01-24 : 07:40:42
|
| oh well thanks for the advice but meanwhile i wrote my own function that accepts 2 parameters: string and field. For example@val = getField('aa|bb|cc',2) will return 'bb'@val = getField('aa||cc',2) will return null. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-24 : 09:27:51
|
Good for you!
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
Instr from oracle to charindex question