How to extract text after a given string to look for.

Question

Post reply

How to extract text after a given string to look for.

TollHouse

Old Hand

Points: 331
More actions
February 5, 2021 at 12:59 pm
Go to Answer
#3841497

I am a forever learning new DBA. I have been handed a project I have no idea how to complete. I have a table that has a column called settings. In that column there is a massive string of data. Within that string of data I must extract out a single setting. The text I need to extract is as follows.
"IsFso": after that text there could be a value of true or false. Now I don't know which value it will be on hundreds of databases having this setting. So I need to figure out how to get that information.
Is this even possible? I have attempted using substring but it seems I need to know the text to be able strip out the single piece I need. Any help would be greatly accepted, even if there is no available SQL solution for my project.

Phil Parkin

SSC Guru

Points: 247003

February 5, 2021 at 1:50 pm

Answer

#3841529

TollHouse wrote:

That is correct. And I don't know which it will be.

Doesn't matter – search for both.

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData (LongString VARCHAR(8000));

INSERT #SomeData (LongString)
VALUES
('dlfkjh zlghz lhzg ljshfgv ljhg "IsFso":truezxvlkzçfbhkjzçfbhxçdbjhzxddçjbkh')
,('dlfkjh zlghz lhckljshdf sdjkfsdfhgsdjkdhfgsdjhfgzg ljshfgv ljhg "IsFso":falsezxvlkzçfbhkjzçfbhxçdbjhzxddçjbkh')
,('nothing to be found here');

SELECT
    sd.LongString
,   IsFso = (CASE
                 WHEN pos.TruePos > 0 THEN
                     'true'
                 WHEN pos.FalsePos > 0 THEN
                     'false'
                 ELSE
                     'Unknown'
             END
            )
FROM #SomeData sd
    CROSS APPLY
(
    SELECT
        TruePos = CHARINDEX('"IsFso":true', sd.LongString)
    ,   FalsePos = CHARINDEX('"IsFso":false', sd.LongString)
) pos;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247003 More actions · Answer 1

So the string will contain either

"IsFso":true

or

"IsFso":false

Is that correct?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

TollHouse Old Hand Points: 331 More actions · Answer 2

That is correct. And I don't know which it will be.

TollHouse Old Hand Points: 331 More actions · Answer 3

WOW This works like a champ! Your code is so simple and I learned something new today. Thank you again for your assist on this.

ben.brugman SSChampion Points: 13369 More actions · Answer 4

Similar result (see remark)

INSERT #SomeData (LongString)
VALUES
('dlfkjh zlghz lhzg ljshfgv ljhg "IsFso":truezxvlkzçfbhkjzçfbhxçdbjhzxddçjbkh')
,('dlfkjh zlghz lhckljshdf sdjkfsdfhgsdjkdhfgsdjhfgzg ljshfgv ljhg "IsFso":falsezxvlkzçfbhkjzçfbhxçdbjhzxddçjbkh')
,('nothing to be found here');


select *, 'true' IsFso from #SomeData where longstring like '%"IsFso":true%'
Union
select *, 'false' IsFso from #SomeData where longstring like '%"IsFso":false%'
Union
select *, 'Unknown' IsFso from #SomeData where   longstring not like '%"IsFso":true%' and longstring not like '%"IsFso":false%'

If both strings (both true and false) appear in the string, the string will be represented twice in the resultset.

This is just an alternative, I am not commenting that this is better or worse.

Ben

ScottPletcher SSC Guru Points: 101137 More actions · Answer 5

I think it can be simplified a bit, while also allowing for another value to be found in the future (maybe 'null'?):

SELECT
    LongString,
    SUBSTRING(LongString, IsFsoStartOfValue, 
       CASE WHEN SUBSTRING(LongString, IsFsoStartOfValue, 1) = 't' THEN 4 ELSE 5 END) AS IsFsoValue
FROM #SomeData
CROSS APPLY (
    SELECT CHARINDEX('"IsFso":', LongString + '"IsFso":') + 8 AS IsFsoStartOfValue
) AS aliases1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Scott, you forgot about "Unknown".

Here is a simple JOIN version which also uses a single scan through the table and is less dependent on hardcoded values:

DECLARE @Ind varchar(20) ;
SET @Ind = '"IsFso":';

SELECT sd.LongString, ISNULL(st.st, 'Unknown') IsFSO
FROM #SomeData sd
LEFT JOIN (
select CAST('True' as varchar(20)) 
UNION ALL 
select 'False'
) st (St) ON SUBSTRING(sd.LongString, CHARINDEX(@Ind, sd.LongString) + LEN(@Ind), LEN(st.st) ) = st.st

_____________
Code for TallyGenerator