SQL Server Forums
Profile | Register | 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
 New Topic  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

3593 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

3593 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000