Reverse string search (right to left)?

Database
Enthusiast

Reverse string search (right to left)?

How do do a reverse string search? I want to find the position of the last dash in the string starting on the right and scanning to the left. The number of dashes are unknown and some strings may have none.

Part list last dash from right
AAA-BB-LL 3
ZZ-WWWWW 6
NNNNNNN 0
16 REPLIES
Teradata Employee

Re: Reverse string search (right to left)?

This sql will search a string from left to right using recursive SQL. The output is the LAST section of the string where string is divided with the "_" character. You can probably modify it to search from right to left:

CREATE SET TABLE foo (
myname VARCHAR(100),
id integer
)
PRIMARY INDEX ( id );

INSERT INTO FOO VALUES ('I_LIKE_TERADATA',1);
INSERT INTO FOO VALUES ('RED_IS_MY_FAVORITE_COLOR',2);
INSERT INTO FOO VALUES ('SEE_YOU_LATER',3);
INSERT INTO FOO VALUES ('THIS_IS_A_REALLY_LONG_STRING_THAT_IS_REALLY_TOO_LONG',4);

With Recursive LOOKUP (myname, id ) As
(
Select
substr(myname, 1, index(myname,'_')-1) || ' ' || substr(myname, index(myname,'_') +1, character_length(myname)) as myname, id
From foo root
union all
Select
substr(direct.myname, index(direct.myname,'_')+1,100 ) as myname, direct.id
From LOOKUP direct
where index(direct.myname,'_') >0)
select * from LOOKUP b
where index(b.myname,'_')=0

Enthusiast

Re: Reverse string search (right to left)?

Jeff,
I was getting an error while executing the query you provided.

failure 3706 syntax error: expected something between the beginning of the request and the 'with' keyword.

I was using bteq.

Thanks

Teradata Employee

Re: Reverse string search (right to left)?

Are you on V2R6?

WITH RECURSIVE is only supported in V2R6.
Enthusiast

Re: Reverse string search (right to left)?

Thanks!

I am on V2R5
Enthusiast

Re: Reverse string search (right to left)?

Is there an easier way to find the position of the last occurence of a character (say '_') in a string?
Without having to do a lookup join?
Enthusiast

Re: Reverse string search (right to left)?

You could write a UDF. Then, it would just be a function call instead of a join.
Enthusiast

Re: Reverse string search (right to left)?

The UDF solution requires Teradata version V2R5.1 or later.
rgs
Enthusiast

Re: Reverse string search (right to left)?

You might try something like the following. What it does:

1. Takes the string and reverses it. Takes each character and makes the last the first and the second to last the second and so on.
2. Find the position of the first '-' and subtract it from one greater that the string size.
3. use the nullif to return a null if there is no '-' in the string.

create table myt1 (a char(11));

insert into myt1 ('AAA-BB-LL 3');
insert into myt1 ('ZZ-WWWWW 6');
insert into myt1 ('NNNNNNN 0');

select a,
nullif (12 - (position
( '-' IN
substring(a from 11 for 1) ||
substring(a from 10 for 1) ||
substring(a from 9 for 1) ||
substring(a from 8 for 1) ||
substring(a from 7 for 1) ||
substring(a from 6 for 1) ||
substring(a from 5 for 1) ||
substring(a from 4 for 1) ||
substring(a from 3 for 1) ||
substring(a from 2 for 1) ||
substring(a from 1 for 1)
) ), 12) as location
from myt1;

*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

a location
----------- -----------
NNNNNNN 0 ?
AAA-BB-LL 3 7
ZZ-WWWWW 6 3

- Rolf S.
Enthusiast

Re: Reverse string search (right to left)?

Are there plans for a right() function to be added in a future version of Teradata?