CODEWITHSUNDEEP
sqlsql-server
Philip
Philip

Reputation: 2628

Retrieve Text from String where Start and End Characters are the same

How can I get the text between the **** stars considering the begin and end are the same string values?

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'

Upvotes: 0

Views: 517

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521904

You could use the base string functions as follows:

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

Demo

Data:

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

Upvotes: 2

Related Questions