Reverse string search (right to left)?

Database

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

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.

Re: Reverse string search (right to left)?

Thanks!

I am on V2R5
N/A

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?

Re: Reverse string search (right to left)?

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

Re: Reverse string search (right to left)?

The UDF solution requires Teradata version V2R5.1 or later.
rgs
N/A

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.

Re: Reverse string search (right to left)?

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