Re: Leading zero where strlen 5
Hi Scott, all! Scott Haneda wrote: I need to update a column, if the string length is less than 5, I want to add leading zeros to it until it has 5. These are zip codes, I think there are no 00 leading zips, so most should all be four chars long. This sounds like the columns were of a numeric data type. If this is so, IMNSHO it was a wrong decision: Zip codes are character strings, even though they may (in some / many countries) consist of digits only. Use a char (n) column for them, with n varying by country. If you have/want to support international addresses, check for the largest value occurring: Dutch or British postal codes are longer than German or US ones, there may be longer ones elsewhere. Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leading zero where strlen 5
If this is so, IMNSHO it was a wrong decision: Zip codes are character strings, even though they may (in some / many countries) consist of digits only. Use a char (n) column for them, with n varying by country. So did I, as in the char(n) however, the import script I wrote, in the language I was using, being pretty lose at times, has a habit of turning strings to ints, so 01 is 1, and that's what gets sent off to the database. It was after I imported 40,000 of these, that I noticed. I did not want to re-parse all those again, as it takes some time, so I just ran the update and all was well. Thanks everyone. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Leading zero where strlen 5
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
Of course, this will involve you changing the length() if the ZipCode has
only 3 digits.
Also, of course, try this with a LIMIT to make sure that this updates your
fields correctly. I know it works on mine...
J.R.'s $0.02
-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Monday, June 26, 2006 7:17 PM
To: mysql@lists.mysql.com
Subject: Leading zero where strlen 5
I need to update a column, if the string length is less than 5, I want to
add leading zeros to it until it has 5. These are zip codes, I think there
are no 00 leading zips, so most should all be four chars long.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leading zero where strlen 5
J.R. Bullington wrote:
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
How about
UPDATE tbl_Name SET ZipCodes = right(concat('0',ZipCodes), 5) WHERE
length(ZipCodes) 5
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Leading zero where strlen 5
J.R. Bullington wrote:
The best way to do this is with code, however, here is A way to do it (I am
sure that there are more than one...)
UPDATE tbl_Name SET ZipCodes = concat('0',ZipCodes) WHERE length(ZipCodes) =
4
How about
UPDATE tbl_Name SET ZipCodes = right(concat('0',ZipCodes), 5) WHERE
length(ZipCodes) 5
Works, perfect, thanks to both of you.
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
