Frustration Level HI HI HI - formatting text

[kq]

I have a text field on teradata that was converted from a numeric field from the source data. It can be anywhere from 1 to 6 characters, but I need it to pad out with leading zero's to six positions for every other source system I'm using.

I'm new to teradata sql and none of my old sql tricks are working for me! I've read a bit about the converting command (can't think of the command name right now), but can't get it to do what I want it to do.

Is anyone able to HELP???

I'm using both Queryman and Access97 pass through

 

[Patten]

select substring('000000' from 1 for 6-characters('4444'))||'4444'

select substring('000000' from 1 for 6-characters('22'))||'22'

Use your field to be padded instead of '4444' or '22'

 

[tdgulbra]

Unless I am misunderstanding the problem, here is another possibility

Select cast(TextString as Integer) (format'999999');

 

[Patten]

That's right tdgulbra, but your solution can only be used in bteq since odbc connections (like queryman) do not take into account the format clause.

 

[tdgulbra]

True. I missed that line in the original post. Yours is the better solution, Patten.

 

[kq]

IT WORKED, PATTEN!! Thank you so very much! I'm going to share this with everyone who needs to convert this field. What a time/life saver!

Where would I have found this in any kind of documenation?

 

[dnoeth]

Hi tdgulbra,
you _can_ get formatted values with an ODBC conncetion, you just have to cast to a char:
Select cast((cast('123' as Integer) (format'999999')) as char(6));
or plain Teradata syntax
Select '123' (Integer,format'999999') (char(6));

Btw, Patten's solution looks more complicated, but it's using less CPU time ;-)

Dieter

 

[jliang]

Hi,

I try to use substring to pad '0' to the decimal data, I got error 3996 --" Right truncation of string data ". I think the problem is that there are some 0.00 format data. I have another column without zero data, it works fine.

substring('0000000000' from char_length(trim(cast(nms as char(10))) ) + 1) || trim(cast(nms as char(10)))

Thanks very much for your help in advance

Jing

 

[kensha]

I am getting below pasted error while creating a function.
Can anybody guide me to compile the below function.

---------------------------------------
Client : Teradata BTEQ 08.02.00.00 for UNIX5.
DB Version : V2R.05.01.01.08 ( MP-RAS)
---------------------------------------

CREATE FUNCTION squel.plusudf( a INTEGER, b
INTEGER)
RETURNS INTEGER
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!sname!/export/home/sh/fnc/plusudf.c'
PARAMETER STYLE TD_GENERAL;
*** Warning: Unknown response parcel 151
0000 0101 0100 0029 2F65 7870 6F72 742F 686F *.....)/export/ho*
0010 6D65 2F7A 6177 2F74 656D 702F 7368 616A *me/sh/fnc/plusud*
0020 752F 666E 632F 706C 7573 7564 662E 6300 *f.c.............*
0030 0000 0000 0000 0000 0000 0000 0000 0000 *................*
0100 *0000 0000 0000 *......*
*** Error 8006 Invalid message kind.
*** Total elapsed time was 1 second.

 

[tdatgod]

This is the same request posted on TeradataForum.com. please see the repsonses over there.