CODEWITHSUNDEEP
mysql
Abhik
Abhik

Reputation: 1

How to find out a column with carriage return in Mysql?

I am a beginner in SQL and trying to run a query like this:

select id from "a_table" where col_value = 'ABCDEF';

Now in the table a_table, col_value has both

'ABCDEF'

and

'ABCD
EF'

(with a carriage return)

My question is how can I construct my query so that if I search for 'ABCDEF' in the table where ABCDEF is present and may or may not have a carriage return in it.

Upvotes: 0

Views: 749

Answers (3)

Pankaj Kumar
Pankaj Kumar

Reputation: 570

To retrieve data without carriage return please try

    select id from "a_table" where col_value = 'ABCDEF' and col_value NOT REGEXP "\r\n";

Upvotes: 0

Saptarshi Dey
Saptarshi Dey

Reputation: 366

This can solve your issue

select id from "a_table" where replace(col_value,'\n','') = 'ABCDEF';

Upvotes: 2

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try to use replace function

select id
from "a_table"
where replace(replace(col_value, '\r', ''), '\n', '') = 'ABCDEF'

And if you need you can update bad rows

update "a_table"
set
  col_value = replace(replace(col_value, '\r', ''), '\n', '')
where col_value <> replace(replace(col_value, '\r', ''), '\n', '')

But before update it's desirable to check them using the following query

select
  id,
  col_value,
  replace(replace(col_value, '\r', ''), '\n', '') new_value
from "a_table"
where col_value <> replace(replace(col_value, '\r', ''), '\n', '')

Upvotes: 2

Related Questions