CODEWITHSUNDEEP
sql-serverdatabaset-sql
Maggie
Maggie

Reputation: 99

T-SQL Insert number with leading zero into table

I need to insert into my table a number 0000128, but it see this number as 128, I was trying also change type from int to varchar, but there wasn't a difference.

Is there any possibility to do this?

Upvotes: 1

Views: 3793

Answers (3)

SQLburn
SQLburn

Reputation: 129

I have run into this issue before when doing ETL. This is how I solved it.

-- declare and assign
-- this could be from anywhere
declare @int_NUMBER int = 128

-- show the original
select @int_NUMBER

-- steps:
-- first: cast the number as a varchar

-- second: create a string of zeros. The number of zeros should be equal to the max size of this "number". 
-- In this example, the max size this "number" is 7 places.

-- third: concat the leading zeros in front of the varchar of the number

-- forth: take the number of max places from the right
-- this way you will always get the proper amount of leading zeros

select right('0000000' + cast(@int_NUMBER as varchar(10)),7)

Results:

128

(1 row(s) affected)

0000128

(1 row(s) affected)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453067

Storing as varchar should work fine. 

DECLARE @T TABLE (
  V VARCHAR(10));

INSERT INTO @T
VALUES      ('0000128');

SELECT *
FROM   @T

You must be casting to numeric along the way and losing the leading zeroes.

Upvotes: 3

hobbs
hobbs

Reputation: 239841

There is no such thing as a number with a leading zero, only textual representations of numbers with leading zeroes.

Upvotes: 0

Related Questions