Solved

Problem adding extra 0s to the beginning of varchar

Posted on 2008-06-13
8
219 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard 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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 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