Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Probably a newb question, but...

I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original number if it is 10 or greater (i.e. 1=01, 10=10 and 300=300).

SELECT DISTINCT RIGHT('0'+CONVERT(VARCHAR,[FacilityCode]),3) FROM...

This returns 1=01, 10=010 and 300=300 (using the same examples as above)

EDIT: I'm trying to do this within a query so that I can do lookups without altering either table.

Can someone lend a brother a hand here? My brain just ain't working.

share|improve this question
    
Which RDBMS? SQL Server? Oracle? –  Mark Sinkinson Jul 24 at 14:57
    
Generally with the left pad (LPAD) function, but for a one time thing the update code is good too. –  user44339 Jul 24 at 15:19
    
Any particular reason you don't want 1 to be 001 ? –  RolandoMySQLDBA Jul 24 at 15:20
    
RolandoMySQLDBA - The related table has varchar values which follow the example above. Mark, I'm using MSSQL 2008 r2. All... I'm trying to do this through a query in order to select items in a related table which formats the numbers (which are actually varchars) in the manner illustrated above. –  SnagQueensHubby Jul 24 at 15:32
    
@user44339 SQL Server doesn't have any such function. –  Martin Smith Jul 24 at 20:10

4 Answers 4

up vote 7 down vote accepted

The safest way is probably to only add zeroes when the length of the column is 1 character:

UPDATE
   Table
SET
   MyCol = '0' + MyCol
WHERE
   LEN(MyCol) = 1

This will cover all numbers under 10 and also ignore any that already have a leading 0.

EDIT

To just select the data try:

SELECT
   CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END
FROM
   MyTable
share|improve this answer
    
This is great, but I'm trying to do this through a query, without having to update my table. Updating my table, at this point, would break a bunch of stuff... I should have been more specific.. –  SnagQueensHubby Jul 24 at 15:36
    
Then just make it a select... –  JNK Jul 24 at 15:43
    
I understand where you were going with this, and it works great, however, it only returns values under 10... I'm looking to return all values with any that are less than 10 padded... Thanks JNK, I appreciate the assist (as I do from all of you!). –  SnagQueensHubby Jul 24 at 15:53
    
Try the new version... –  JNK Jul 24 at 16:08
1  
@SnagQueensHubby That was an alias (misplaced during the previous JNK's edit.) It should be SELECT MyColPadded = CASE ... END –  ypercube Jul 24 at 17:12

(it looks from your example code that you are using MS SQL Server, but you should add an appropriate tag (and/or body text) to your question to indicate this if so, or if not which DBMS you are actually using)

Any of the options above should work fine, I would generally use the "prefix 0s then use RIGHT() option as you have but the others are perfectly valid), but a word of warning if you expect new rows (or updates to existing rows) to contain low numbers - if that is possible then you will end up with unpadded values creeping back in. To avoid this you could use either an AFTER or INSTEAD OF trigger to force values into the right format as they are inserted/updated to stop the problem coming back (without needing to change all code points that save to that table). Be careful to verify performance though: triggers can be very powerful but it is also easy to create a performance nightmare with them.

share|improve this answer
UPDATE MyTable
SET MyColumn = '0' + Mycolumn
WHERE MyColumn < '10'
AND MyColumn NOT LIKE '0%';
share|improve this answer
2  
You need to add AND Mycolumn NOT LIKE '0%' otherwise you will have multiple zeroes. –  JNK Jul 24 at 15:08
    
Good catch, fixing now –  Phrancis Jul 24 at 15:10
1  
The OP stated that the minimum value is 1, but in general, if you want at least two digits, and you have zero, you do want to add another zero, so I would use LEN (as in JNK's answer) rather than LIKE. –  hvd Jul 24 at 19:47
3  
The logic behind the answer is sane but the answer is not correct for a very different reason than the above comments. MyColumn is a varchar, so the condition MyColumn < '10' will not result in TRUE when the value in the column is '2' or '3' or ... '9'. For char values '9' < '10' is false. –  ypercube Jul 25 at 9:32
1  
Agree with @ypercube. You could change the first comparison to MyColumn < 10 but that might break as we don't know if MyColumn is guaranteed to ever contain only numbers. –  Andriy M Jul 25 at 10:16

You can use a case statement for this, if it must be formatted in the database and you can't persist it.

Select case when len(facilityCode) = 1 then 
          '0' else '' end + facilityCode as facilityCode
From YourTable

It sounds like you are doing this as a FK, if so you can't have a FK constraint on a calculation. To make doing the join easier, you might consider a calculated column.

 ALTER TABLE yourTable 
    ADD cFacilityCode AS (
      case when len(facilityCode) = 1 then '0' else '' end + facilityCode);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.