Problem adding extra 0s to the beginning of varchar

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
LVL 2
Steven O'NeillSolutions ArchitectAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Steven O'NeillSolutions ArchitectAuthor Commented:
It's an nvarchar in the data table
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Steven O'NeillSolutions ArchitectAuthor Commented:
yes it is
0
 
Steven O'NeillSolutions ArchitectAuthor Commented:
sorry code hasn't worked
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indeed ...
right('00000' + COALESCE( Contact_Migration_Ref, cast( Contact_Migration_Ref_ID as nvarchar(5)) ) ,5) 

Open in new window

0
 
Steven O'NeillSolutions ArchitectAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.