CODEWITHSUNDEEP
sqlsql-server
Zhu
Zhu

Reputation: 3895

How to insert a value starting with 0 in NVARCHAR column SQL Server

I am new to SQL Server, I am trying to insert a 6 digit string value into a NVARCHAR column. But it stores the data perfectly until the 6 digit string starts with 0.

Ex: 001212 -->stores as 1212
    121212 -->stores as 121212
    012121 -->stores as 12121
    120121 -->stores as 120121

I searched for the solution on stackoverflow but I can get solution for this .

But I have an idea that validate the string length if it is not 6 digit then adding 0 .

if it is correct can someone help me to do it .

Upvotes: 2

Views: 1999

Answers (2)

sticky bit
sticky bit

Reputation: 37472

If it's a string work with it like it's a string. That is, enclose literal values in single quotes (and add the Nprefix for nvarchars).

INSERT INTO elbat (nmuloc)
VALUES (N'000007');

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Technically what you want to do is right pad the number. SQL Server doesn't have a built in function, but it is not to hard to write an expression to do it:

select right(replicate('0', 6) + convert(varchar(255), @num), 6)

Upvotes: 3

Related Questions