CODEWITHSUNDEEP
phpmysql
Harinder
Harinder

Reputation: 1257

Trim first few characters from front with mysql

i have a table in mysql with column comp_post_code in whivh postcode format is with no space M4W1A3 but some of the postcodes are like this with AB T1H2J1, BC V6X3P7, QC G0C2R0 and so on.I want to remove this extra bit of AB BC from them.

how can i do that in mysql ... (AB BC QC is just example there r many others in them, but all are in front and with space and postcode dont have space)

EDIT ... I tryed this 

UPDATE canada_database
SET comp_post_code= SUBSTRING(comp_post_code, 3)

But its deleting 1st 3 from all rows ... as i have mention there are some with this problem not all .. if i do this it will affect all .

Upvotes: 1

Views: 155

Answers (3)

Amir
Amir

Reputation: 4111

UPDATE canada_database
SET comp_post_code = if(LOCATE(' ',comp_post_code) > 0 , SUBSTRING(comp_post_code , 3) , comp_post_code)

Upvotes: 0

tanaydin
tanaydin

Reputation: 5316

as an alternave way... you can select your data with regexp query...

select * from canada_database where comp_post_code regexp '[A-Z]{2} [A-Z]*';

then iterate througt them and update each with PHP... cause mysql doesn't support regexp-based replace.

Upvotes: 0

piotrm
piotrm

Reputation: 12356

UPDATE canada_database SET comp_post_code = substring_index( comp_post_code, ' ', -1 )

Manual: substring_index().

See an example on sqlfiddle.

Upvotes: 5

Related Questions