Repairing Data packing with zeros

2005-03-25 Thread zzapper 
Hi,
I needed to repair some data that had been entered inconsistently, I was in a 
rush so I did it
manually via SqlYOG. (too much of a rush to consider how to do it quickly!)

The bad data in one column propertyID

was of kind 
\w\w\w\d  eg ONE1
\w\w\w\d\d eg ABC23
\w\w\w\d\d\d eg FGH123

when they all needed to packed with zeros as necessary

\w\w\w\d\d\d\d  

so 
ONE1-  ONE0001
ABC23   - ABC0023
FGH123 - FGH0123

What Update Query should/could I have used?




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Repairing Data packing with zeros

2005-03-25 Thread Keith Ivey 
zzapper wrote:
ONE1-  ONE0001
ABC23   - ABC0023
FGH123 - FGH0123
What Update Query should/could I have used?
Something like this perhaps?
UPDATE table_name SET propertyID = CONCAT(LEFT(propertyID, 3), 
LPAD(SUBSTRING(propertyID, 4), 4, '0'))
WHERE propertyID REGEXP '^[A-Z]{3}[0-9]{1,3}$';

--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Repairing Data packing with zeros

2005-03-25 Thread zzapper 
On Fri, 25 Mar 2005 12:03:26 -0500,  wrote:

zzapper wrote:

 ONE1-  ONE0001
 ABC23   - ABC0023
 FGH123 - FGH0123
 
 What Update Query should/could I have used?

Something like this perhaps?

UPDATE table_name SET propertyID = CONCAT(LEFT(propertyID, 3), 
LPAD(SUBSTRING(propertyID, 4), 4, '0'))
WHERE propertyID REGEXP '^[A-Z]{3}[0-9]{1,3}$';
Keith,
Thanx that works just fine




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]