All Forums Analytics
Jagdish 14 posts Joined 08/07
16 Aug 2007
How can we Reverse a String in Teradata

Hi,How can we reverse a string in teradata.For example a string 'STAGINGAREA' need to be reversed to 'AERAGNIGATS'.thanksjagdish

RGlass 35 posts Joined 09/04
16 Aug 2007

select substr('STAGINGAREA',(chars('STAGINGAREA')),1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -1,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -2,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -3,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -4,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -5,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -6,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -7,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -8,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -9,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -10,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -11,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -12,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -13,1) ||substr('STAGINGAREA',(chars('STAGINGAREA')) -14,1);

Jim Chapman 449 posts Joined 09/04
16 Aug 2007

Or create a UDF.

joedsilva 505 posts Joined 07/05
16 Aug 2007

As jim suggested, check if you can do it via a UDF. But there are lots of shops who would say a no to using UDFs...This is a small (& not very tidy) example of using recursive SQL to do the same task. Not to mention uses up quite a bit of spool :-) Pay specific attention to the data type conversions used to avoid trouble later ;-) CREATE TABLE DATA005( EID INTEGER NOT NULL PRIMARY KEY ,NAME VARCHAR(30) NOT NULL);INSERT INTO DATA005 VALUES(1, 'JOEY BOY');INSERT INTO DATA005 VALUES(2, 'CALVIN');INSERT INTO DATA005 VALUES(3, 'HOBBES');INSERT INTO DATA005 VALUES(4, '');WITH RECURSIVE REVNAME(EID, NAME, NAMELEN, LVL, RNAME)AS(SELECT EID, NAME, CHARACTER_LENGTH(NAME), 1(INTEGER), '' (VARCHAR(30))FROM DATA005UNION ALLSELECT EID, NAME, NAMELEN, LVL+1, SUBSTRING(NAME FROM LVL FOR 1) || RNAMEFROM REVNAMEWHERE LVL <= NAMELEN)SELECT EID, NAME, RNAMEFROM REVNAMEWHERE NAMELEN+1 = LVL; EID NAME RNAME 1 JOEY BOY YOB YEOJ 2 CALVIN NIVLAC 3 HOBBES SEBBOH 4

Jagdish 14 posts Joined 08/07
17 Aug 2007

thank you very much everyone.thanks joeit worked.

TT.Srivatsan 9 posts Joined 03/11
05 Apr 2011

i want to update RNAME into the name column in DATA005 how to do that

Help me

You must sign in to leave a comment.