INSTR Function

Database
r04
N/A

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
N/A

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
N/A

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.