0

I have a table which uses utf8 charset and utf8 binary collation for text fields.
I want all rows in the table which are base64 encoded.
So my query is: 

select * from test where field REGEXP '^([A-Za-z0-9+/]{4})*([A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=)?$' ;

But this query matches rows where the field has a binary value like �ÀJÒŽ©%ù¶±üÊ=.
The problem with my table is that I am storing binary strings in text fields and I want to base64 encode all such strings.
The regex for base64 encoding match was taken from this question RegEx to parse or validate Base64 data
I think it matches accented characters as well. But I thought regexp worked on byte strings.

Community
  • 1
  • 1
Shikhar Subedi
  • 596
  • 1
  • 8
  • 25

1 Answers1

0

Try 

field REGEXP '...' COLLATE utf8_bin

If you have � in a field, the 'text' in that field is probably irrecoverable.

Rick James
  • 106,233
  • 9
  • 103
  • 171