how to display zero preceding a number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to display zero preceding a number

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-03-07 : 14:09:29

Hi,

I have a query similar to below


case when matrix = 'seven' then '07' 
when matrix = 'eight' then '08' else '00' end from table

The output displays only '7' instead of '07' and so for eight also.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-07 : 14:50:12

quote:
Originally posted by learning_grsql

Hi,

I have a query similar to below

case when matrix = 'seven' then '07'
when matrix = 'eight' then '08' else '00' end from table

The output displays only '7' instead of '07' and so for eight also.

That is probably because you are selecting from a numeric column. If it is a character column (or if it is similar to what you have shown above), it should print the prefixed zero. See the example below - this should display the prefixed zero.

CREATE TABLE #tmp (matrix VARCHAR(32));
INSERT INTO #tmp VALUES ('seven'),('eight'),('nine');
SELECT
case when matrix = 'seven' then '07' 
when matrix = 'eight' then '08' else '00' end from #tmp

If it is numeric, cast it to varchar(nn).

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-03-07 : 15:06:27

the column it is selecting from is nvarchar data type. I tested your code and it seems working fine.

As per your advise, i changed code as shown below but it still didn't work


case when cast(matrix as varchar(50)) = 'seven' then '07'
when cast(matrix as varchar(50)) = 'eight' then '08' else '00' end from table

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-07 : 15:27:09

Can you post the entire query? The fragment you have posted displays the prefixed zeros; so it is something else in the query that is stripping off the zeros. Can't imagine what that might be without seeing the code.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-03-09 : 07:36:41

Thanks James,

It works now. Actually, I put else 0 instead of '00'. That was causing the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources