SQL query to select strings that contain a "Unit Separator" character

Question

I have table like this

I want get those record which content Unit Separator

I have try many things but not getting result.I try with char(31) and 0x1f and many other ways but not getting desired result.This is my query which i try

SELECT * FROM `submissions_answers` WHERE `question_id`=90 AND `answer` like '%0x1f%'

How can i do this? Please help me..

Answer 1

Problem

The expression you tried won't work because answer LIKE '%0x1f%' is looking for a string with literally '0x1f' as part of it - it doesn't get converted to an ASCII code.

Solutions

Some alternatives to this part of the expression that ought to work are:-

1. answer LIKE CONCAT('%', 0x1F, '%')
2. answer REGEXP 0x1F
3. INSTR(answer, 0x1F) > 0

Further consideration

If none of these work then there may be a further possibility. Are you sure the character seen in the strings is actually 0x1F? I only ask because the first thing I tried was to paste in ␟ but it turns out MySQL see this as a decimal character code of 226 rather than 31. Not sure which client you are using but if the 0x1F character is in the string, it might not actually appear in the output.

Demo

Some tests demonstrating the points above: SQL Fiddle demo

Answer 2

You can use:

SELECT * FROM submissions_answers WHERE question_id=90 AND instr(answer,char(31))>0

The keyword here being the INSTR MySQL function, which you can read about here. This function returns the position of the first occurrence of substring (char(31)) in the string (answer).