CODEWITHSUNDEEP
mysql
Roi patrick Florentino
Roi patrick Florentino

Reputation: 177

Select all columns with ascii code in mysql

I have a code which I recently discovered :) and it does do its job and well done I might add. But, I want to check all columns instead of checking it by column. Is it possible

Check my code below:

SELECT column_name
FROM table_name
WHERE column_name REGEXP '[[.DLE.]-[.US.]]'

Now, what I want is something like this but it won't work

SELECT *
FROM table_name
WHERE REGEXP '[[.DLE.]-[.US.]]'

Kindly advice and I apologize for asking many questions :)

Upvotes: 0

Views: 225

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

REGEXP is a binary operator which means you have to have a left operand and a right operand.
Like most arithmetic operators.

You could check all columns like this:

SELECT *
FROM table_name
WHERE CONCAT(a, b, c, d, ...) REGEXP '[[.DLE.]-[.US.]]'

I'm using ... for the sake of the example, but you'd need to name all your columns explicitly. There's no option to use a wildcard for the columns inside an expression.

You can't set "all columns" in a single SET clause. You'd need to do something like the following:

UPDATE table_name SET
  a = REPLACE(a,char(16),''),
  b = REPLACE(b,char(16),''),
  c = REPLACE(c,char(16),''),
  d = REPLACE(d,char(16),''),
  ...similar for other columns;

If you think this is an unexpected omission in the SQL language, then I wonder if you can name any other programming language that lets you compare to or assign a value to "all variables" in a single expression?

Upvotes: 2

Related Questions