Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to find and replace a word in a mysql column?

Tags:

I have a column containing list of streets. I need to replace 'street' with 'St'. The replacement can be made in the current column or in a new column with the address in the required format. The following is the sample data. 'Column 1' contains the data in current format. 'Column 2' contains the data in the desired format.

Column 1         Column 2 Hillary Street   Hillary St Golf Road        Golf Road Oldwood Street   Oldwood St 

How do I do this?

Edit:

This query works for this:

UPDATE table SET column = REPLACE(column, 'Street', 'St'); 

Is it possible to set a rule to this column. Such that all data added to this get formatted this way automatically? Or I need to repeat this query each time?

like image 520
Shyam Natraj Kanagasabapathy Avatar asked May 30 '11 01:05

Shyam Natraj Kanagasabapathy


People also ask

How do you change a value in a column in MySQL?

MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.

How do I replace a character in a string in MySQL?

MySQL REPLACE() Function The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: This function performs a case-sensitive replacement.


2 Answers

Run a query like this to update in the same column:

UPDATE table     SET column = REPLACE(column, 'Street', 'St'); 
like image 151
AJ. Avatar answered Sep 20 '22 10:09

AJ.


So, if i understand correctly, you want to modify the data on the database based on wether or not you're using the word street. I think this is the call u need.

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]'); 

I think this is the method you need to use, so your ending code will look something like

update myTable set address = replace(address,'street','St'); 

Is this what you meant?

like image 30
Andres C Avatar answered Sep 21 '22 10:09

Andres C