Cacher is the code snippet organizer for pro developers

-- note: a left join is required because City is an EAV attribute
-- i.e. if a customer has no City information, then there will be no
-- corresponding entry in the customer_address_entity_varchar db table

SELECT customer_address_entity.entity_id
FROM customer_address_entity
LEFT JOIN customer_address_entity_varchar ON (
    customer_address_entity.entity_id = customer_address_entity_varchar.entity_id
    AND customer_address_entity_varchar.attribute_id IN (
        -- get "city" attribute id
        SELECT attribute_id
        FROM eav_attribute
        WHERE entity_type_id = 2
          AND attribute_code = 'city'
    )
)
WHERE customer_address_entity_varchar.value IS NULL
ORDER BY entity_id;


-- TODO - run the following SQL on the customer_address_entity_varchar db table for every single entry returned by the previous SQL:
--
-- INSERT INTO customer_address_entity_varchar(`entity_type_id`, `attribute_id`, `entity_id`, `value`)
-- VALUES (2, 24, $id, 'SG');

-- fix customer address errors
UPDATE customer_address_entity_varchar
SET value = 'SG'
WHERE entity_type_id = 2
  AND attribute_id = 26;
  
UPDATE customer_address_entity_varchar
SET value = 'SG'
WHERE entity_type_id = 2
  AND attribute_id = 27;

-- fix invalid postal codes
UPDATE customer_address_entity_varchar
SET value = stripAlpha(value)
WHERE entity_type_id = 2
  AND attribute_id = 30
  AND value REGEXP '[^0-9]';

DROP FUNCTION IF EXISTS stripAlpha;

-- taken from http://stackoverflow.com/a/5146376
delimiter //

create function stripAlpha(str varchar(50)) returns varchar(50)
no sql
begin
declare verification varchar(50);
declare result varchar(50) default '';
declare chr varchar(2);
declare i integer default 1;

if char_length(str) > 0 then
    while(i <= char_length(str)) do
        set chr = substring(str,i,1);
        set verification = find_in_set(chr,'1,2,3,4,5,6,7,8,9,0');
 
        if verification > 0 then
            set result = concat(result,chr);
        end if;
 
        set i = i + 1;
 
    end while;
 
    return result;
else
    return '';
end if;
end //
 
delimiter ;