How can we Reverse a String in Teradata

Analytics

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'.

thanks
jagdish
5 REPLIES
N/A

Re: How can we Reverse a String in Teradata

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);

Re: How can we Reverse a String in Teradata

Or create a UDF.

Re: How can we Reverse a String in Teradata

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 DATA005

UNION ALL

SELECT EID, NAME, NAMELEN, LVL+1, SUBSTRING(NAME FROM LVL FOR 1) || RNAME
FROM REVNAME
WHERE LVL <= NAMELEN
)
SELECT EID, NAME, RNAME
FROM REVNAME
WHERE NAMELEN+1 = LVL
;

EID NAME RNAME
1 JOEY BOY YOB YEOJ
2 CALVIN NIVLAC
3 HOBBES SEBBOH
4

Re: How can we Reverse a String in Teradata

thank you very much everyone.
thanks joe
it worked.

Re: How can we Reverse a String in Teradata

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

Help me