时间戳

首页 > 解决方案 > 从开始和结束字符相同的字符串中检索文本

sql - 从开始和结束字符相同的字符串中检索文本

问题描述

考虑到开始和结束是相同的字符串值，如何获取****星之间的文本？

Create Table #temp
(
    TestString varchar(400)
)

insert into #temp
(
    TestString
)
select
    '**** mary had a little lamb****'
union
select
    '**** humpy dumpty had a great fall**** All the king''s horses and all the king''s men'

标签： sqlsql-server

解决方案

您可以按如下方式使用基本字符串函数：

SELECT
    TestString,
    SUBSTRING(TestString,
              CHARINDEX('****', TestString) + 4,
              CHARINDEX('****', TestString, CHARINDEX('****', TestString) + 1) - 5) AS contents
FROM yourTable;

演示

数据：

WITH yourTable AS (
    SELECT '**** mary had a little lamb****' AS TestString UNION ALL
    SELECT '**** humpy dumpty had a great fall**** All the king''s horses and all the king''s men'
)

推荐阅读