remove leading Zeros while importing any column from text file to SQL database

Question

remove leading Zeros while importing any column from text file to SQL database

kanth.vishal

SSC Journeyman

Points: 93
More actions
March 16, 2010 at 1:59 pm

#71899

Hello,
Please help me in resolving an issue. I have text file with one of the column as value 0000002008,I need to trim the leading zeros and only insert 2008 value to the database table.
Can this be handled on Data Column Transformation , if yes then please suggest the expression that will help in achieving it..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Have you tried transforming it to an int, and then back to a string? That seems like it should do what you're trying to accomplish.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kanth.vishal SSC Journeyman Points: 93 More actions · Answer 2

No I was not able to do that , can you please help me in doing this

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

WayneS (3/16/2010)
Have you tried transforming it to an int, and then back to a string? That seems like it should do what you're trying to accomplish.

Ummm... why would you convert it back to a string?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)