Last post Sep 12, 2016 09:37 AM by wmec
Sep 03, 2016 02:24 PM|grafic.web|LINK
I have a db called Salons and i would love to upate with a SQL query all my salons where the field SalonPostalCode len is less than 5 with adding a 0 in front of SalonPostalCode
An the same if SalonPostalCode len is less than 4 with adding a 00 in front of SalonPostalCode
How to do?
Sep 04, 2016 09:44 PM|mbanavige|LINK
If your postal codes are all in a 5 digit format, then you could do something this:
update sometable SET postalcode = RIGHT('00000' + postalcode,5) WHERE len(postalcode) < 5
if you also have some postal codes with the extra 4 digit suffix then you'd need to get a little more creative than the above.
Sep 05, 2016 05:44 AM|eralper|LINK
Your requirement is known as zero padding in different programming languages.
You can use the UDF user defined function udfLeftSQLPadding in tutorial
padding leading zeros in SQL
And call it as
Sep 08, 2016 10:52 AM|balu.devara|LINK
can you try bellow code.
declare @colLength int
SELECT @colLength=COL_LENGTH('sometable', 'postalcode');
select REPLICATE(0,@colLength-LEN(postalcode))+postalcode from sometable
Sep 12, 2016 09:37 AM|wmec|LINK