All Forums Database
barani_sachin 141 posts Joined 01/12
08 Jan 2012
Removing leading or trailing tabs...

I want to remove leading and trailing tabs in a column... I tried trim function and its not working... is there any other means we can remove the leading or trailing tabs...

ulrich 816 posts Joined 09/09
09 Jan 2012

you can use a simple substr 

like 

case when substr(cola,1,1) = '\t' then substr(cola,2) else cola end

for leading

'\t' need to be replaced with a tab...

and 

case when characters(cola) = 1
                 then case when cola = '\t' then '' else cola end

        else   case case when substr(cola,charcaters(cola),1) = '\t' then substr(cola,1,characters(cola)-1) else cola end

end

if you are on 13.1 make SQL UDFs out of this to have nicer code

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
09 Jan 2012

And check also 

http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs

there are some very useful udfs...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
09 Jan 2012

Or just use TRIM(BOTH '09'xc FROM col)

Dieter

Dieter

ulrich 816 posts Joined 09/09
09 Jan 2012

:-) - yes, this is nice, I always forget this option in trim...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

barani_sachin 141 posts Joined 01/12
10 Jan 2012

Thanks Dieter.. it works like charm... I am new to teradata... will u please tell me whats this option means ('09'xc)... what are the other values that we can use it in here...

Ulrich your method didnt yield the expected results...

You must sign in to leave a comment.