0

Using MySQL 5, I have around 100000 rows in a table.

In addition to the alphanumerical characters some of the rows have other characters like special characters for a column, let's say column1.

I want to find them.

The solution which came to my mind is writing a query like this:

  1. The inner query replaces the alphanumerical characters with ''
  2. The outer query is: SELECT * FROM (INNER QUERY) WHERE column1 NOT LIKE ''

Inner query:

SELECT REPLACE(...(REPLACE(REPLACE((column1 ,'a',''...

It is hard to use REPLACE for 26 letters and 10 digits one by one in the query. Is there any way to do it a better way?

Appreciate for any idea or if there is a better solution for my requirement.

Cid
  • 14,968
  • 4
  • 30
  • 45
G. Hak.
  • 361
  • 2
  • 3
  • 11
  • @Cid My SQL 5 I mention at the first sentence – G. Hak. Sep 05 '19 at 15:19
  • @G.Hak. you can get some ideas from [MySQL strip non-numeric characters to compare](https://stackoverflow.com/questions/287105/mysql-strip-non-numeric-characters-to-compare) . Basically, you will need to use a UDF – Madhur Bhaiya Sep 05 '19 at 15:55
  • Do you just want to find the values or do you want to change them? – Nick Sep 06 '19 at 04:13

0 Answers0