Solved

Problem adding extra 0s to the beginning of varchar

Posted on 2008-06-13
8
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Replace Dates in query 14 58
View SQL 2005 Job package 16 79
SQL Error - Query 6 55
SQL Need to calculate age from DOB, no stored procedures or functions or views 3 42
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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