MySQL replace numbers after hyphen

Question

Hi,

I have a large table with ZIP codes but they are in long format like : 06608-1405

What I require is to REMOVE the hyphen and the numbers after the hyphen. I have using MYSQL REPLACE before but not sure how if you can use Wildcards.

Cheers for you time.

J

Answer 1

If the numbers before the hypen are all the same length (5), you can do SUBSTRING(zip, 0, 5)

Or even SUBSTRING(zip, 0, LOCATE('-', zip))

Or LEFT(zip, LOCATE('-', zip)) if you wanna follow the other suggestion that was made.

Answer 2

use a combination of the left() the instr() functions

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

i.e.

select left('12345-6789', instr('12345-6789', '-') - 1)

returns

12345

(could/should be refined to only carry out left() operation if instr() returns a non-zero value).

EDIT: to remove the hyphen etc. use:

update my_table
set my_col = left(my_col, instr(my_col, '-') - 1)

Answer 3

If they're all ZIP codes (i.e., 5 digits, hyphen, 4 digits), you can just use:

mysql> SELECT LEFT(table.zip_code, 5); // (06608-1405 -> 06608)

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_left

Answer 4

How about using SUBSTRING_INDEX:

select SUBSTRING_INDEX(ZIP, '-', 1);

Edit:

To update the table:

Update <table-name> set ZIP = SUBSTRING_INDEX(ZIP, '-', 1);