Trimming Inner Spaces

Database

Trimming Inner Spaces

Hello, I have an address field that is storing way too many spaces between some of the words. For example...

123 Fake Address N

How would I TRIM that large amount of spaces between 'Address' and 'N'?

I found the below piece of coding which is supposed to address this issue, though it doesn't seem to work with Teradata SQL.

replace(replace(replace(primary_address,' ','<>'),'><',''),'<>',' ')

5 REPLIES
N/A

Re: Trimming Inner Spaces

The replace function does not rexist on Teradata.
There is an oreplace UDF available on the Teradata website. See :
http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions

You may need to get your DBA's to install these for you. The command would then be:

oreplace(oreplace(oreplace(primary_address,' ','<>'),'><',''),'<>',' ')

Re: Trimming Inner Spaces

Hi,

I have a similar requirement as above where if the column value is A01 567 8  ,  then while selecting from the table i need the value as  A015678. Is there any other way as oreplace is not working in teradata.

Appreciate a fast response.

Thanks,

Neetu

Re: Trimming Inner Spaces

Hi Neetu,

you can try

select

'A01 567 8 ' as X,

trim(substring(X from 1 for 1)) ||

trim(substring(X from 2 for 1)) ||

trim(substring(X from 3 for 1)) ||

trim(substring(X from 4 for 1)) ||

trim(substring(X from 5 for 1)) ||

trim(substring(X from 6 for 1)) ||

trim(substring(X from 7 for 1)) ||

trim(substring(X from 8 for 1)) ||

trim(substring(X from 9 for 1)) ||

trim(substring(X from 10 for 1))

Refer to this post http://www.teradataforum.com/teradata/20040108_114251.htm

Re: Trimming Inner Spaces

Or you can create a procedure like this: 

you can give the input string that need to be modified and the string that need to be trimmed:

REPLACE PROCEDURE  TEST123DJ  (

IN INPUTSTRING VARCHAR(100),

IN CH2TRIM VARCHAR(30),

OUT P_PROCMSG VARCHAR(100)

)

DYNAMIC RESULT SETS 1

BEGIN

DECLARE VARIABLE1 VARCHAR(100) ;

DECLARE VARIABLE2 INTEGER;

DECLARE VARIABLE3 VARCHAR(30) ;

DECLARE VARIABLE4 INTEGER;

DECLARE VARIABLE5 INTEGER;

DECLARE VARIABLE6 VARCHAR(100) ;

DECLARE results1 CURSOR WITH RETURN ONLY FOR

select :VARIABLE1 Dynamic_Result_Set1;

FOR REC AS TEST CURSOR FOR

SEL :INPUTSTRING INPUTSTRING,:CH2TRIM CH2TRIM

DO

SET VARIABLE1=TRIM(REC.INPUTSTRING);

SET VARIABLE2=CHAR_LENGTH(VARIABLE1);

IF REC.CH2TRIM=' ' THEN

SET VARIABLE3=REC.CH2TRIM;

ELSE

SET VARIABLE3=TRIM(REC.CH2TRIM);

END IF;

SET VARIABLE4=CHAR_LENGTH(VARIABLE3);

SET VARIABLE5= POSITION( VARIABLE3 IN VARIABLE1);

WHILE VARIABLE5>0 DO

SET VARIABLE6=SUBSTR(VARIABLE1,1,VARIABLE5-1)||SUBSTR(VARIABLE1,VARIABLE5+VARIABLE4,VARIABLE2);

SET VARIABLE1=VARIABLE6;

SET VARIABLE2=CHAR_LENGTH(VARIABLE1);

SET VARIABLE5= POSITION( VARIABLE3 IN VARIABLE1);

SET P_PROCMSG=TRIM(VARIABLE1);

END WHILE;

SET P_PROCMSG='The result of Re4place Function is: '||TRIM(VARIABLE1);

END FOR;

OPEN results1;

END;

Testing:

call   Test123dj   ('Teest 12 3 3','1',:P_PROCMSG);

call   Test123dj   ('Teest 12 3 3','3',:P_PROCMSG);

call   Test123dj   ('Teest 12 3 3','e',:P_PROCMSG);

call   Test123dj   ('Teest 12 3 3',' ',:P_PROCMSG);

call   Test123dj   ('Teest 12 3 3','st',:P_PROCMSG);

The procedure gives the final string as a dynamic result set or output message; if needed please modify the procedure to include DBC.SysExecSQL procedure to get the final string to a table.

 

Re: Trimming Inner Spaces

Here is with recursive function....

Just change this 1234567890 as what we need to replace in a string

with recursive r2(id, st, L, c, rstr) as

(

sel id,str as st, char(st) as L, 1 as c, trim(case when position(substr(st,1,1) in '1234567890') > 0 then ''else substr(st,1,1) end) as rstr from revString

union all

sel r2.id,r2.st, char(st) as L, c+1 as c, rstr || trim(case when position(substr(r2.st,c+1,1) in '1234567890') > 0 then ''else substr(r2.st,c+1,1) end) as rstr from  r2

where L <> c

)sel * from r2 order by id,4 desc ;