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.
February 5, 2021 at 1:03 pm
So the string will contain either
"IsFso":true
or
"IsFso":false
Is that correct?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 5, 2021 at 1:12 pm
That is correct. And I don't know which it will be.
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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 5, 2021 at 1:55 pm
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.
February 5, 2021 at 3:06 pm
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
February 5, 2021 at 9:02 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 7, 2021 at 12:23 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply