How to convert int to char without delete zero value

Question

How to convert int to varchar in SQL Server without deleting zero value?

For example this query

Select CONVERT(varchar,002000089)

results in:

How can I get 002000089 as the query result?

002000089 isn't an int, an int can't have preceding zero's. So you're trying to convert a string to a string. — HoneyBadger
– HoneyBadger, Commented Nov 23, 2020 at 10:22
Pick the right data type for your data. If you have something in which leading zeroes are relevant, then its a string of digits, not a number. Don't ever get it near an int variable/column, which is designed for storing numbers. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 23, 2020 at 10:57

Gordon Linoff · Accepted Answer · 2020-11-23 14:37:35Z

1

You have to left pad them. A pretty simple method is to use format():

select format(002000089, '000000000')

answered Nov 23, 2020 at 14:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tuan Bao · Accepted Answer · 2020-11-23 10:49:17Z

I have two way to get the result 002000089 in sql-server

Append the converted result: 00 + CONVERT(varchar,002000089)

declare @intValue int=002000089 select '00'+ convert(varchar,@intValue)

I attach the converted result to my meta varchar value as 000000000 by stuff

declare @intValue int=002000089 declare @metaValue varchar(20)='000000000' print stuff( @metaValue, len(@metaValue)-len(@intValue)+1, len(@intValue), cast(@intValue as varchar))

Collectives™ on Stack Overflow

How to convert int to char without delete zero value

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related