INSTR Function

Database
r04
Fan

INSTR Function

Hey i consider the string below

REFGTYT*156*THU2

I want the Result 156 to be displayed( that is between the Asterix symbol) in Oracle we will be using INSTR function how to proceed this in TEREDATA

REgards,
Balaji
4 REPLIES
Teradata Employee

Re: INSTR Function

Hello,

Your best bet will be the UDF, otherwise you can use multiple combinations of SUBSTR and index functions to get the desired results.

Regards,

Adeel
r04
Fan

Re: INSTR Function

as im new to Teredata ,can u plz Get the UDF for me...

also can u point out any website where we can chk how the same Querys are used forOracle,MYSQL,Teredata etc.,
r04
Fan

Re: INSTR Function

hi,
i tried
SUBSTR (CLMN , (INDEX (CLMN , '*') +1), (INDEX (CLMN , '*') -1))
it worked well...
gee
N/A

Re: INSTR Function

Hi that's not 100% correct since the result is 156*THU2.

SELECT
'REFGTYT*156*THU2' AS CLMN,
SUBSTR (CLMN, (INDEX (CLMN, '*') +1), INDEX ( SUBSTR(CLMN, INDEX (CLMN, '*') +1, CHAR_LENGTH(CLMN) - (INDEX (CLMN, '*') ) ), '*') - 1 )

is much better.