SQL查询以选择包含“单位分隔符”字符的字符串

I have table like this

我有这样的表

I want get those record which content Unit Separator

我想得到那些内容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

我尝试了很多东西，但没有得到结果。我尝试使用char（31）和0x1f以及许多其他方法，但没有得到理想的结果。这是我的查询，我尝试

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

How can i do this? Please help me..

我怎样才能做到这一点？请帮帮我..

4 个解决方案

#1

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).

这里的关键字是INSTR MySQL函数，您可以在这里阅读。此函数返回字符串（answer）中第一次出现的substring（char（31））的位置。

#2

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.

您尝试的表达式将无法正常工作，因为答案LIKE'％0x1f％'正在查找字符串为'0x1f'作为其一部分的字符串 - 它不会转换为ASCII代码。

Solutions

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

应该起作用的这部分表达的一些替代方案是： -

1. answer LIKE CONCAT('%', 0x1F, '%')
2. 回答LIKE CONCAT（'％'，0x1F，'％'）
3. answer REGEXP 0x1F
4. 回答REGEXP 0x1F
5. INSTR(answer, 0x1F) > 0
6. INSTR（答案，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.

如果这些都不起作用那么可能还有其他可能性。你确定在字符串中看到的字符实际上是0x1F吗？我只是问，因为我尝试的第一件事就是粘贴␟但事实证明MySQL将其视为226的十进制字符代码而不是31.不确定您使用的是哪个客户端但是如果0x1F字符在字符串中，则实际上可能不会出现在输出中。

Demo

Some tests demonstrating the points above: SQL Fiddle demo

一些测试证明了以上几点：SQL小提琴演示

#3

Yet another way...

还有另一种方式......

SELECT * FROM `submissions_answers`
    WHERE `question_id`=90
      AND HEX(`answer`) REGEXP '^(..)*1F'

Explanation of the regexp:

正则表达式的解释：

• ^ - start matching at the beginning (of answer)
• ^ - 在开头（答案）开始匹配
• (..)* -- match any number (*) of 2-byte things (..)
• （..）* - 匹配任何数字（*）的2字节内容（..）
• then match 1F, the hex for US.
• 然后匹配1F，美国的十六进制。

#4

You could convert the answer column into a HEX value, and then look for values containing that hex string.

您可以将答案列转换为HEX值，然后查找包含该十六进制字符串的值。

SELECT * FROM `submissions_answers`
WHERE HEX(`answer`) LIKE '%E2909F%'