前端之家

  1. 首页
  2. 问答

有没有一种优雅而有效的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|

欢迎提出任何想法。

lts0129 回答：有没有一种优雅而有效的SQL方式来列出所有子字符串位置？

对于SQL Server具有递归CTE：

with cte as (
  select id,charindex('st',text) pos from tablename
  union all
  select t.id,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',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
full-text-searchmysqlsqlsubstring
本文链接：https://www.f2er.com/3123335.html

大家都在问