?
Solved

Problem adding extra 0s to the beginning of varchar

Posted on 2008-06-13
8
Medium Priority
?
226 Views
Last Modified: 2010-03-19
I have the following code in one of my SELECT statements:

case when Contact_Migration_Ref is not null then right('00000' + cast(Contact_Migration_Ref as varchar(5)),5) else Contact_Migration_Ref_ID end

Contact_Migration_Ref is being pulled in and has a value like 01234 or 00070 but the beginning 0s where being removed from my code so I added the section:

right('00000' + cast(Contact_Migration_Ref as varchar(5)),5)

into my code above (as is laid out) but this hasn;t helded att the starting 0s this time around for some reason...it's worked in the past.

Any ideas how I can rectify this problem?

Thanx
0
Comment
Question by:Steven O'Neill
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21776735
what do you "do" with the value after/with the select?
if the result is int data type, leading "0" will indeed be removed.
if the data type is kept varchar, the leading "0" will not be removed.
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 21776760
It's an nvarchar in the data table
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21776873
Contact_Migration_Ref_ID is integer, right?
right('00000' + cast( COALESCE( Contact_Migration_Ref, Contact_Migration_Ref_ID ) as varchar(5)),5) 

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:Steven O'Neill
ID: 21776881
yes it is
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 21776894
sorry code hasn't worked
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21776911
indeed ...
right('00000' + COALESCE( Contact_Migration_Ref, cast( Contact_Migration_Ref_ID as nvarchar(5)) ) ,5) 

Open in new window

0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 21777020
Sorry nada. Not sure why you're bringing Contact_Migration_Ref_ID into this as it's a different record that has a different number in it in case there is nothing in the Contact_Migration_Ref record. We only use this is the Contact_Migration_Ref record is NULL.

I've tried this:

case when Contact_Migration_Ref is not null then right('00000' + cast(Contact_Migration_Ref as varchar(5)),5) else right('000000' + cast(Contact_Migration_Ref_ID as varchar(6)),6) end

and it appears to work but I'm assuming here that SQL is interpreting Contact_Migration_Ref_ID as an int in my original query and as such then everyhting there will be classified as an int.

Now although the above code works fine is it the ideal way to do this?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 21777045
>Not sure why you're bringing Contact_Migration_Ref_ID into this
because of your initial syntax:

case when Contact_Migration_Ref is not null then right('00000' + cast(Contact_Migration_Ref as varchar(5)),5) else Contact_Migration_Ref_ID end

so, in case Contact_Migration_Ref is null, use  Contact_Migration_Ref_ID  instead...
that's what my suggestion with COALESCE() will do also.

recap:
* Contact_Migration_Ref is nvarchar(5)
* Contact_Migration_Ref_ID  is int
* when Contact_Migration_Ref is null, use Contact_Migration_Ref_ID instead
* format the value with leading '0', up to 5 characters

then, this simple formulas should do:


right('00000' + COALESCE( Contact_Migration_Ref, cast( Contact_Migration_Ref_ID as nvarchar(5)) ) ,5) 
 
or
right('00000' + CASE WHEN  Contact_Migration_Ref IS NULL THEN CAST(Contact_Migration_Ref_ID as nvarchar(5)) ELSE Contact_Migration_Ref END, 5)

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based developeā€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovā€¦
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question