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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to display zero preceding a number
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 03/07/2014 :  14:09:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 03/07/2014 :  14:50:12  Show Profile  Reply with Quote
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).
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 03/07/2014 :  15:06:27  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 03/07/2014 :  15:27:09  Show Profile  Reply with Quote
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.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 03/09/2014 :  07:36:41  Show Profile  Reply with Quote
Thanks James,

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

Edited by - learning_grsql on 03/09/2014 07:41:28
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000