December 13, 2004 at 5:27 am
I am trying to use DTS Transformation to extract from a table to a text file. I have a quantity field defined as an integer and I need the output text file to have leading zeros. Does anyone have a solution for writing to a text file with leading zeros for a column that is selected from a table?
December 13, 2004 at 6:46 am
Hi there
A simple solution would appear to be to write a UDF capable of creating your required output and then include this in your select from your table
eg. select name, dob, padnum(numfield)
this could then return your data in the required format without having to alter source or destination.
Post back if you need a pad function
December 13, 2004 at 6:49 am
Please provide an example of the the pad function.
Thanks
December 13, 2004 at 7:03 am
I suggest you writer a select query for your source and change you object like so.
Ex (Assumes a column for zip code defined as int with constraint 00000-99999)
SELECT
RIGHT('00000' + CAST(zip as varchar(5)),5) AS Zip
FROM
...
December 13, 2004 at 7:35 am
Hi, try this. Allows padding of any character to any length
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create function padstring(@thestring varchar(50),@finlength int,@padchar char(1))
returns varchar(50)
as
begin
if len(rtrim(@thestring))<@finlength
begin
while len(rtrim(@thestring))<@finlength
begin
set @thestring=@padchar+@thestring
end
end
return @thestring
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
so
select dbo.padstring('8',3,'0')
will give you 008
December 14, 2004 at 6:33 am
or replace the 'while' block with
SET @thestring = REPLICATE(@padchar, @finlength-LEN(RTRIM(@thestring))) + @thestring
--
Scott
December 14, 2004 at 9:08 am
yes, that would work too, and a little more elegant !
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply