SQL Server Performance Forum – Threads Archive
Exporting Data to Comma-Delimited File DTSHi, all. Is there a way using DTS when you export data to a comma-delimited file to pad a column value with zeroes? For example, if the number/value in the column is 5, we would like the value in the comma-delimited file to be 000005. And, the value in the comma-delimited file is a string. I’m thinking I could get the length of my number from my SELECT statement and then based on the length, pad in the appropriate number of zeros to the front of my number in the Active X Script (transformation task). I’m kind of struggling with the syntax right now. Thanks, Sue
Hi, all. Okay, I think I’ve got it figured out. I am using the REPLICATE function in my SQL query
so I say something like:
SELECT REPLICATE(‘0’, 6-(LENGTH(EmpID)) & CAST(EmpID AS VarChar(6)) where 6 is the max length of my output string, 6 minus LENGTH(EmpID) is how many zeroes I want to append to the front end of my string, and then I concatenate it casting my EmpID as a VarChar with a length of 6. – Sue
Hi all, You could use this too.
SELECT RIGHT(‘000000’ & CAST(field_name as char(6)),6) or SELECT RIGHT(‘000000’ & CAST(COALESCE(field_name,0) as char(6)),6) if you allow NULL values for this column.