Leading Zeros

edison · Post by **edison** » Tue May 08, 2012 9:47 am

Input is a txt file and output table.

Input

0001
0002
0003
0001,0002
0005
0003,0004

Need out put like

01
02
03
01,02
05
03,04

chulett · Post by **chulett** » Tue May 08, 2012 10:55 am

Investigate the FMT() function if we are talking about string fields here. What is the datatype of these columns in the target table?

ray.wurlod · Post by **ray.wurlod** » Tue May 08, 2012 4:08 pm

The FMTS() function will be of even more use.

Code: Select all

Convert(@VM,",",Fmts(Convert(",",@VM,InLink,TheString),"2R"))

The inner Convert() changes commas to value marks.
The Fmts() function applies the format specification to each value in the multi-valued field.
The outer Convert() changes the values marks back to commas.

chulett · Post by **chulett** » Tue May 08, 2012 4:29 pm

FMTS... plural? Not familiar with that one or at least it's not ringing a bell, need to look it up later, it would seem.

ray.wurlod · Post by **ray.wurlod** » Tue May 08, 2012 6:56 pm

Plural = multi-valued, yes.

chulett · Post by **chulett** » Tue May 08, 2012 8:24 pm

Ah, I see...