How To Find Out How Many Zero Present And Display The Zero In SQL Table

Question

SQL Query For Create Table

CREATE TABLE Test
(ID int,
 Value1 int,
 Value2 int,
)

SQL Query For Insert Value

  INSERT INTO Test
  VALUES(001,200,300)

OutPut

ID    Value1    Value2
1     200       300

Query To Display 00 In Result

SELECT RIGHT('00' + CAST([ID] AS varchar(5)) , 3),Value1,Value2
FROM Test

Now The Output Is

001 200   300

My Id Value Is maximum 5 Digit

Example 00001,00010,00100,01000,10000

Now i Want To Display The Result Like

   00001,00010,00100,01000,10000

I Need A Query Automatically Find Out How Many Zero Present Before The Number

Example 00001 This Value Have 4zero , 00010 This Value Have 3 Zero.

How To Use This Query

 SELECT RIGHT('00' + CAST([ID] AS varchar(5)) , 3),Value1,Value2
 FROM Test

For Find Out How Many Zero Present And Display The Result With zero

`001` isn't a valid int. So you can't put `001`into an integer column. — Liam, Jan 08 '14 at 10:31
Please explain what you want to do. '001' is a string, not a number. If you want to format 1 as '001' for display purposes, you can do it with the proper SQL statement — Panagiotis Kanavos, Jan 08 '14 at 10:36
@Liam I Wrongly Add Question Before Now I Changed Plz Tell Me The Solution — Gurunathan, Jan 08 '14 at 11:26
@Liam my Account Is Locked To Ask New question So Plz provide Me Any Solution — Gurunathan, Jan 08 '14 at 11:38
whoops, someone's been asking some bad questions. Read the info on [question bans](http://stackoverflow.com/help/question-bans) — Liam, Jan 08 '14 at 11:45
@Gurunathan, BTW, looking at your account, you don't tend to accept answers very often. If you accept some good answers this might give you enough rep to get over the ban...maybe? — Liam, Jan 08 '14 at 12:10
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44779/discussion-between-gurunathan-and-liam) — Gurunathan, Jan 08 '14 at 12:42

Thorsten Dittmar · Accepted Answer · 2014-01-08T11:12:30.497

Numeric data types don't contain leading zeros. If you need to store leading zeros, you need to use a character datatype.

If you do that, however, to store numbers without leading zeros, please note that sorting on a character datatype field is done lexicographically, so you might end up with something like this:

Also, you should note the difference between data storage and data display. You could well store numbers without leading zeros, but make your application display them containing leading zeros!

As other people have noted, something like

SELECT RIGHT('00' + CONVERT(NVARCHAR, Field), 3)

will help you select the content of a numeric field as a string with leading zeros.

You could also do that in C# using

String.Format("{0:d3}", fieldvalue);

score 3 · Answer 2 · answered Jan 08 '14 at 10:35

You are basically saying "I want to store a non-integer value in an integer column, how do I do that?"

Now, the obvious answer is of course: you don't.

The real question is, why do you think you want this? If (as I suspect) it is for later display purposes, then forget about inserting invalid data and focus on the real issue: how do I display my data the way I want to?

And the answer to that is simply": anywhere but in your database :)

You can change your integer to a string before displaying, either in your select statement, or further on in your front end.

score 1 · Answer 3 · answered Jan 08 '14 at 10:32

1

You'd have to format the number manually. One way to do that:

select  right('00' + cast(ID as varchar(3)),3)

answered Jan 08 '14 at 10:32

Andomar

232,371
49
380
404

score 1 · Answer 4 · answered Jan 08 '14 at 10:38

1

SELECT RIGHT('00' + CAST([ID] AS varchar(5)) , 3)
FROM test

The 3 is the number of characters you want total in the output display

answered Jan 08 '14 at 10:38

Nagaraj S

13,316
6
32
53

Your Solution Is Help Full For Me But I Need Some Functionality I Edited My Code now Plz Check – Gurunathan Jan 08 '14 at 11:28

score 0 · Answer 5 · edited Jan 08 '14 at 13:43

0

First you can't insert the value 00002 or anything like that with zeros (only in int type)... so the concept is fully wrong... or else change your data type to varchar then you can do these things...

if you are using varchar then simply use the following function

reverse()

edited Jan 08 '14 at 13:43

Liam

27,717
28
128
190

answered Jan 08 '14 at 10:39

vino20

429
4
13

How To Find Out How Many Zero Present And Display The Zero In SQL Table

5 Answers5