0

hello I want to remove numeric values from column.

enter image description here

Please let me know what would be the query to remove all the numeric values from mysql database

Note:

This Note is for those who are marking this question as duplicate

Please understand the difference. I don't want to "select" the string values from column. I want to completely remove the integer values or numeric values from column.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
hellosheikh
  • 2,929
  • 8
  • 49
  • 115
  • 2
    Possible duplicate of [Strip out digits/numeric chars from a mysql string column](http://stackoverflow.com/questions/3177974/strip-out-digits-numeric-chars-from-a-mysql-string-column) – jszobody Mar 02 '16 at 18:27
  • Removing my duplicate suggestion from the comments here since I didn't realize that the numeric value is sitting in the same record as the string. yucko. @jszobody's suggested duplicate is correct. Kind of partial to the `regex_replace` udf suggestion. – JNevill Mar 02 '16 at 18:39
  • @JNevill no jszobody's suggested duplicate is not correct. that answer is for the select query. I need the update query. Hope you understand the difference – hellosheikh Mar 02 '16 at 18:42
  • 1
    You can't translate that answer into an UPDATE query by yourself? It's a basic CRUD operation. – JNevill Mar 02 '16 at 18:55

1 Answers1

1

First check if it will fix according as follow:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(city_name, '\n', 1), '\n', -1) FROM yourtable;

If you're sure, do the following:

UPDATE yourtable
SET city_name = SUBSTRING_INDEX(SUBSTRING_INDEX(city_name, '\n', 1), '\n', -1);

Reference: How to split the name string in mysql?

Community
  • 1
  • 1
Tin
  • 794
  • 5
  • 10