[RESOLVED] add leading zero to column if it doesnt exist

mackin

I have a column of Area codes - some of which have a leading zero and some dont (due to uploading them from excel spreadsheet which in its wisdom and my bad formatting removed some)

I need a function to check if the code starts with a zero and if not to add one.

I thought of using pad but need to check if the first character is a zero first

can u suggest what I need to do?

NogDog

Would it make more sense to check the length of the zip code rather than what the leading character is? (E.g., in the US, at least, "0123" would still be an invalid Zip Code.) If so, then you could just use str_pad() or printf() to zero-pad to 5 characters.

PS: If there is some good reason to base it on whether or not the first character is "0", you could use substr(), or just treat the string as an array and look at the value of $varName[0].

mackin

Hi NoDog, they are not zipcodes - but telephone area codes in UK - so some may be 01229 and some 020.

My phonetic code if there is such a thing is.

If est_code starts with 0 then do nothing else add zero

Derokorian

if( substr($est_code,0,1) != '0' ) $est_code = '0'.$est_code;

mackin

excellent, works a treat Derokorian, thank you.:rolleyes:

bradgrafelman

Couple things.. note that this:

substr($est_code,0,1) != '0'

could be reduced to this:

$est_code[0] != '0'

And also note that doing this in PHP is altogether unnecessary; why loop through each row individually, retrieve its contents, use PHP to determine if it needs to change, make the change, and then replace the row's contents? Why not simply let the SQL server take care of it for you, e.g.:

UPDATE myTable
SET theColumn = CONCAT('0', theColumn)
WHERE LEFT(theColumn, 1) != '0'

mackin

updating the column certainly makes more sense and Seeing the code I will probably do that now.

The php code worked well, and it only does one area code at a time so wasnt too intensive - but anything to improve loading time cant be bad.

Thanks for the tips