Solved

Problem adding extra 0s to the beginning of varchar

Posted on 2008-06-13
8
195 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 142

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 142

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Divide by zero error encountered. 2 39
Impove long SQL Stored Procedure Performance 14 73
Why do I get extra rows when I do inner join? 12 39
Help  needed 3 22
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now