mysql - 是否有一种优雅而有效的SQL方式列出所有子字符串位置？

我在sql数据库中有一个文本列。我的任务是在文本中查找给定字符串的所有出现，并逐个文本列出字符位置（以供稍后在应用程序中反向引用）。

我发现像this这样的示例可以通过while循环解决类似的问题。但是，我不喜欢编写循环的想法，因为存在一种更苗条的方法。

我想这与STRING_SPLIT in T-SQL类似，尽管我强调我最好是在寻找MySQL解决方案。 STRING_SPLIT返回一列表，其中填充了拆分字符串的子字符串。一种假想的ALL_POSITIONS方法可以返回一列表，该表填充了文本中匹配项的起始位置；如果没有匹配项，则返回一个空表。或者，为了进行JOINing，可能会有另一列作为主键引用。

因此，让我们以一个示例表来说明我的观点：

|Id|Text                      |
+--+--------------------------+
| 0|This is my teststring     |
| 1|A second teststring       |

我梦dream以求的伪SQL：

SELECT ALL_POSITIONS('st', Text, Id) FROM Table;

这将产生：

|Id|Position|
+--+--------+
| 0|      13|    <- the first 'st' where Id = 0
| 0|      15|    <- the second 'st' where Id = 0 etc.
| 1|      11|
| 1|      13|

任何想法都欢迎。

最佳答案

对于SQL Server，使用递归CTE：

with cte as (
  select id, charindex('st', text) pos from tablename
  union all
  select t.id, charindex('st', t.text, c.pos + 1)
  from tablename t inner join cte c
  on c.id = t.id
  where c.pos > 0 and c.pos < len(t.text)
)
select * from cte
where pos > 0
order by id, pos

请参见demo。
对于MySql 8.0+：

with recursive cte as (
  select id, locate('st', text) pos from tablename
  union all
  select t.id, locate('st', t.text, c.pos + 1)
  from tablename t inner join cte c
  on c.id = t.id
  where c.pos > 0 and c.pos < length(t.text)
)
select * from cte
where pos > 0
order by id, pos

请参见demo。
结果：

> id | pos
> -: | --:
>  0 |  14
>  0 |  16
>  1 |  12
>  1 |  14

关于mysql - 是否有一种优雅而有效的SQL方式列出所有子字符串位置？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58803732/