Find several methods for fixing and replacing strings in the mysql field
- 2020-05-26 07:56:27
- OfStack
Our requirement is to remove China from the address field and set the province's (province_id) field according to the address field.
1. To find strings, we can use replace in mysql, which is also mentioned in this blog. See: https: / / www. ofstack. com article / 31374. htm
Ok, let's get rid of the character "China".
update table set address = replace (address, 'China', 'China')
2. Update the province_id field according to the opening character of the address field and the SQL statement as follows
UPDATE table SET province_id = 11 where LEFT (address, 2) = 'fujian'
This is the LEFT function of mysql, which looks up the first two characters of the address character to see if it is' fujian 'and if it is, sql will update the province_id field to 11. The 11 here is ID corresponding to fujian province table.
1. To find strings, we can use replace in mysql, which is also mentioned in this blog. See: https: / / www. ofstack. com article / 31374. htm
Ok, let's get rid of the character "China".
update table set address = replace (address, 'China', 'China')
2. Update the province_id field according to the opening character of the address field and the SQL statement as follows
UPDATE table SET province_id = 11 where LEFT (address, 2) = 'fujian'
This is the LEFT function of mysql, which looks up the first two characters of the address character to see if it is' fujian 'and if it is, sql will update the province_id field to 11. The 11 here is ID corresponding to fujian province table.