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?
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.
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.
Run a query like this to update in the same column:
UPDATE table SET column = REPLACE(column, 'Street', 'St');
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With