Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pad varchar with leading zeros Expand / Collapse
Author
Message
Posted Monday, November 25, 2002 11:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 14, 2013 7:12 PM
Points: 43, Visits: 28
I have a varchar (5) filed that I would like to pad with leading zeros. The length of each entry is from 1-5 chars long. If the length is less than 5 I would like to pad it with zeros to make it 5 characters long. I am not sure how to do this. Thanks for any help
Kevin-

Thanks For your continued Help.



Thanks For your continued Help.
Post #8400
Posted Monday, November 25, 2002 11:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 14, 2013 7:12 PM
Points: 43, Visits: 28
Nevermind. I was able to develop the correct syntax. the below worked well when length of field is 3:

update table_name
set field_name = ('00'+field_name)
where len(field_name) = 3

Thanks For your continued Help.



Thanks For your continued Help.
Post #47643
Posted Monday, November 25, 2002 12:40 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 9:43 AM
Points: 164, Visits: 29
If you want a generic function you could do it like this:
 
SET field_name = RIGHT(REPLICATE('0',5) + field_name,5)


-Dan



-Dan
Post #47644
Posted Monday, November 25, 2002 6:45 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, September 26, 2014 11:48 AM
Points: 8,370, Visits: 742
Or

REPLICATE('0', 5-LEN(field_name)) + field_name




Post #47645
Posted Thursday, August 7, 2014 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:15 AM
Points: 1, Visits: 2
This worked like a charm!
Post #1600818
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse