All Forums Database
TimLind53 5 posts Joined 10/04
24 Mar 2006
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 3ZZ-WWWWW 6NNNNNNN 0

j355ga 100 posts Joined 12/05
17 May 2006

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, idFrom foo rootunion allSelect substr(direct.myname, index(direct.myname,'_')+1,100 ) as myname, direct.idFrom LOOKUP directwhere index(direct.myname,'_') >0)select * from LOOKUP bwhere index(b.myname,'_')=0

Jeff

18 May 2006

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

j355ga 100 posts Joined 12/05
18 May 2006

Are you on V2R6? WITH RECURSIVE is only supported in V2R6.

Jeff

18 May 2006

Thanks!I am on V2R5

ysrinu 44 posts Joined 04/07
12 Apr 2007

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?

Barry-1604 176 posts Joined 07/05
16 Apr 2007

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

Jim Chapman 449 posts Joined 09/04
16 Apr 2007

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

rgs 106 posts Joined 02/07
26 Apr 2007

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 7ZZ-WWWWW 6 3- Rolf S.

teradatatester 69 posts Joined 01/10
06 Apr 2011

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

dnoeth 4628 posts Joined 11/04
07 Apr 2011

I don't know, but it's easy to rewrite:
right(col, 3) -> substring(col from char_length(col) - 2)

In TD13.10 you simply put this into a SQL UDF

Dieter

Dieter

teradatauser2 236 posts Joined 04/12
24 Oct 2012

Hi Diether,
I need to find 4 char from the right of a column. I tried substring(col from char_length(col) for  - 4). But this gives me an error. I think we can't use -4. This is not supported. What is the other option for this.
 
Thanks
 

ulrich 816 posts Joined 09/09
24 Oct 2012

select cast((current_date (format 'DD-MM-YYYY'))as char(10)) as txt, substring(txt from characters(txt) - 3 for 4);

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

teradatauser2 236 posts Joined 04/12
24 Oct 2012

Hi Ulrich,
Thanks  ! This logic worked..
I used : substring ( l_comment  from ( character_length(l_comment) -4 ) for 5 )

eric_td 10 posts Joined 04/13
09 Jan 2015

 
Hi,
I am also looking for similar operation
But the query substring ( l_comment  from ( character_length(l_comment) -4 ) for 5 ) 
select cast((current_date (format 'DD-MM-YYYY'))as char(10)) as txt, substring(txt from characters(txt) - 3 for 4);
works fine when we know that the position of the '-' is 4 fixed at 4 positions from the right.
 
I want to separate fields like example

Field_source         O/p field1      O/p field2

abc def 234          abc def           234

xyzyzy                   xyzyzy 

24 hours 5656        24 hours          5656

High7stars 675       High7stars        675   

 

 

 

I have used the function position('003' in char2hexint(translate(col_name using latin_to_unicode))) to find the postion of number in string.

But this works fine for cases of first two examples, but fails when a number is present in middle etc.

 

Basically I need to find the position of first space or first non-digit from the back. If I get that then I can use substr and divide.

 

Please let me know of any possible workaround.

Thanks.

 
 

Rohan_Sawant 55 posts Joined 07/14
09 Jan 2015

Hi Eric_td,
The below query would do the required

CREATE MULTISET VOLATILE TABLE VT_TEST
(
  TXT VARCHAR(30)
)
ON COMMIT PRESERVE ROWS;
 
INSERT INTO VT_TEST ('abc def 234');
INSERT INTO VT_TEST ('xyzyzy');
INSERT INTO VT_TEST ('24 hours 5656');
INSERT INTO VT_TEST ('High7stars 675');

WITH RECURSIVE REMOVE_SPACE (TXT,SPACE_REMOVED,LEVELS) AS
(
  SELECT	
    TXT
  , SUBSTRING(TXT FROM INDEX(TXT,' ') + 1) AS SPACE_REMOVED
  , 1 AS LEVELS
  FROM	
  	VT_TEST
  UNION ALL
  SELECT 
    A.TXT
  , SUBSTRING(A.SPACE_REMOVED FROM INDEX(A.SPACE_REMOVED,' ') + 1) AS SPACE_REMOVED
  , LEVELS + 1
  FROM	
  	REMOVE_SPACE A
  JOIN
  	VT_TEST B
  ON A.TXT = B.TXT
  AND INDEX(SPACE_REMOVED,' ') <> 0
)
SELECT
DISTINCT	
  A.TXT
, CASE
	WHEN TRIM(OREPLACE(A.TXT,A.SPACE_REMOVED)) = ''
	THEN A.TXT
	ELSE OREPLACE(A.TXT,A.SPACE_REMOVED)
  END	AS FIELD1
, OREPLACE(A.TXT,FIELD1) AS FIELD2
FROM	
	REMOVE_SPACE A
INNER JOIN
(
  SELECT
    TXT
  , MAX(LEVELS) AS MAX_LEVELS
  FROM
  	REMOVE_SPACE
  GROUP BY 1
) B
ON A.TXT = B.TXT
AND A.LEVELS = B.MAX_LEVELS;

Please let me know in case of issues.
Thanks,
Rohan Sawant

shobhit 1 post Joined 03/11
15 Jan 2015

Hello Eric/Rohan,
One possible approach without using recursive queries - 

CREATE MULTISET VOLATILE TABLE VT_TEST

(

  TXT VARCHAR(30)

)

ON COMMIT PRESERVE ROWS;

  

INSERT INTO VT_TEST ('abc def 234');

INSERT INTO VT_TEST ('xyzyzy');

INSERT INTO VT_TEST ('24 hours 5656');

INSERT INTO VT_TEST ('High7stars 675');

 

 

 select TXT as inputfield1,  

 case when index( TXT,' ' ) > 0 then 

  substr( TXT  ,1,INSTR( TXT ,' ' ,1,    character(  TXT  ) - character( oreplace( TXT ,' ' ,''  )   )      ) -1)  

  else TXT

  end as ofield1 

 ,case when  index( TXT,' ' ) > 0 then 

  substr( TXT  ,  INSTR( TXT ,' ' ,1,   character(  TXT  ) - character( oreplace( TXT ,' ' ,''  )   )    )    )  

  else NULL  end as ofield2

  from VT_TEST   ;

 

Output - 

 

inputfield1          ofield1         ofield2

High7stars 675  High7stars 675

xyzyzy               xyzyzy         ?

24 hours 5656  24 hours     5656

abc def 234       abc def        234

 

Regards
Shobhit

You must sign in to leave a comment.