mysql - substring_index 从右侧跳过分隔符

Question

我有一个表“car_purchases”，其中包含“描述”列。该列是一个字符串，其中包含名字首字母、后跟句号、空格和姓氏。描述列的示例是

'Car purchased by J. Blow'

我正在使用“substring_index”函数来提取“.”之前的字母在列字符串中。就像这样:

SELECT
Description,
SUBSTRING_INDEX(Description, '.', 1) as TrimInitial,
SUBSTRING_INDEX(
SUBSTRING_INDEX(Description, '.', 1),' ', -1) as trimmed,
length(SUBSTRING_INDEX(
SUBSTRING_INDEX(Description, '.', 1),' ', -1)) as length
from car_purchases;

我将此查询称为 1。

picture of the result set (Result 1) is as follows

正如您所看到的，问题在于 select 语句中的“修剪”列开始计算第二个分隔符“”，而不是右侧第一个分隔符，并生成“by J”结果，而不仅仅是“J”。此外，长度列表明字符串长度是 5 而不是 4，所以 WTF？

但是当我执行以下 select 语句时；

select SUBSTRING_INDEX(
SUBSTRING_INDEX('Car purchased by J. Blow', '.', 1),' ', -1); -- query 2

结果 =“J”为“结果 2”。

正如您从结果 1 中看到的，“描述”列中的字符串与“结果 2”中的字符串完全相同(据我所知)。但是，当对列(而不仅仅是字符串本身)执行 substring_index 时，结果会忽略第一个分隔符，并从字符串右侧的第二个分隔符中选择一个字符串。

我为此绞尽脑汁，并尝试使用“by”和“by”作为分隔符，但这两个选项都不能产生单个字符的所需结果。我不想通过使用修剪函数来进一步增加查询 1 的复杂性。我还尝试了结果列“修剪”上的强制转换函数，但仍然没有成功。我也不想连接它。

查询 1 的“length”列存在异常，如果我将 length 函数更改为 char_length 函数，如下所示:

select length(SUBSTRING_INDEX(
SUBSTRING_INDEX(Description, '.', 1),' ', -1)) as length -- result = 5

select char_length(SUBSTRING_INDEX(
SUBSTRING_INDEX(Description, '.', 1),' ', -1)) as length -- result = 4

谁能向我解释一下为什么上面的 select 语句会产生 2 个不同的结果？我认为这就是我没有得到我想要的结果的原因。

但要明确的是，我想要的结果是得到“J”而不是“by J”。

我想我可以尝试反向，但我不认为这是一个可以接受的妥协。另外，我不熟悉排序规则和字符集原则，只是使用默认值。

各位玩家加油!!!

Answer 1

CHAR_LENGTH返回字符长度，因此具有 4 个 2 字节字符的字符串将返回 4。 LENGTH但是返回以字节为单位的长度，因此具有 4 个 2 字节字符的字符串将返回 8。结果中的差异(包括 SUBSTRING_INDEX )表明 by 和 J 之间的“空格” 实际上并不是一个单字节空格(ASCII 0x20)，而是一个看起来像空格的 2 字节字符。要解决此问题，您可以尝试使用 CONVERT 将所有 unicode 字符替换为空格。和 REPLACE 。在此示例中，我在 by 和 J 之间的字符串中有一个 en-space unicode 字符。 CONVERT 将其更改为 ?，然后 REPLACE 将其转换为空格:

SELECT SUBSTRING_INDEX( SUBSTRING_INDEX("Car purchased by J. Blow", '.', 1),' ', -1)

输出:

by J

使用CONVERT和REPLACE:

SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(REPLACE(CONVERT("Car purchased by J. Blow" USING ASCII), '?', ' '), '.', 1),' ', -1)

输出

J

对于您的查询，您可以将字符串替换为您的列名称，即

 SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(REPLACE(CONVERT(description USING ASCII), '?', ' '), '.', 1),' ', -1)

Demo on DBFiddle