CODEWITHSUNDEEP
sqlsql-server-2008
Deka
Deka

Reputation: 131

How to format numeric value when casting to varchar?

I have query similar to this:

DECLARE @value decimal(8,0) = 1

SELECT (CAST @value AS varchar(8))

How can I get output formatted with leading zeros (00000001, 00000023, 00000623 etc.)? How can I do that?

It is simple task in .Net or Java, but I must do it inside view.

Upvotes: 1

Views: 667

Answers (5)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

declare @value varchar(8)='623';
Select ltrim(right(replicate(0,8) + CAST (@value AS varchar(8)),8))

SQL Fiddle demo

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

You can use REPLICATE and RIGHT to do this, like so:

SELECT RIGHT(REPLICATE('0', 8) + CAST(@valueAS VARCHAR(8)), 8)

Live Demo

Upvotes: 1

vkamayiannis
vkamayiannis

Reputation: 735

Try this

SELECT RIGHT('00000000' + CAST (@value AS varchar(8)),8)

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13486

try this:

DECLARE @value decimal(8,0) = 1
SELECT REPLICATE('0',8-len(@value))+CAST(@value AS varchar(8))

Upvotes: 1

Ivan Golović
Ivan Golović

Reputation: 8832

This should work:

DECLARE @value decimal(8,0) = 1

SELECT RIGHT('0000000' + CAST(@value AS varchar(8)), 8)

Upvotes: 2

Related Questions