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
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}$';
--
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,