Find position of a substring

Database
Enthusiast

Find position of a substring

Hi All
Suggest me how to find the position of a character or a substring within a string for the first ,second,third occurences.

Eg:I want the position of third occurence of the character 'a' in the string 'ANAGRAM' i.e i should get the answer as 6.

I think that INSTR is used in Oracle for this purpose.What about in Teradta?Is there any function like that.Can anyone help me in this aspect...

Thnks

5 REPLIES
Enthusiast

Re: Find position of a substring

try using Teradata function POSITION.

POSITION (string_expression_1 IN string_expression_2)

Teradata Manuals says the following.

POSITION is expected to return a value according to the following rules.

* If string_expression_1 has length zero, then the result is one.

* If string_expression_1 is a substring within string_expression_2, the result is one greater than the number of characters (or bits, depending on the string) in string_expression_2 that precede the first occurrence.

* For all other events, the result is zero. Regardless of the character data type, the value for POSITION represents the position of the logical character, not its byte position.
Enthusiast

Re: Find position of a substring

I have tried Position command,but it gave the positon of the first occurence of the required substring.how to get the positions of other occurences?
Enthusiast

Re: Find position of a substring

if you need only the first 3 occurences, try using a combination of SUBSTRING
and POSITION (you will have to nest the function calls).

Lookup the functions and operators manual for syntax.
Enthusiast

Re: Find position of a substring

thnks for ur assistance

Name pls!

Teradata Employee

Re: Find position of a substring

You can also use recursive SQL if you are on V2R6. Search this forum for RECURSIVE.

Good luck!