Splitting Text based on certain phrases in string

Question

Splitting Text based on certain phrases in string

carlmeads1975

SSC Enthusiast

Points: 116
More actions
March 5, 2025 at 3:14 pm

#4551835

I have a text string that contains something similar to below :
[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false},{"ChannelName":"LOT4 - Video","ChannelRecordId":"46c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}]
I need to strip out into separate columns or create additional records for the guid after the text "ChannelRecordID":" and also the false at the end (which might say true). The number of iterations of the phrase ChannelRecordID might also be different in this example its 2 but it could easily be 4,5,6 etc.

Any help would be much appreciated.

Thanks
Carl.

Viewing 11 posts - 1 through 11 (of 11 total)

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

Steve Jones - SSC Editor SSC Guru Points: 738177 More actions · Answer 1

This help?

DECLARE @code VARCHAR(1000) = '[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false},{"ChannelName":"LOT4 - Video","ChannelRecordId":"46c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}]'
SELECT ISJSON(@code)
SELECT 
    JSON_VALUE(value, '$.ChannelRecordId') AS ChannelRecordId
FROM OPENJSON(@code) AS oj

carlmeads1975 SSC Enthusiast Points: 116 More actions · Answer 2

Hi, thanks for replying. its 50% of the way there. splits out my uniqueidentifier perfectly.

I however, also need the false or true part that relates to each identifier, so in the instance of the first channelrecordid it would be false.

'[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}

Is this possible?

Thanks in advance

Carl.

jasona.work SSC Guru Points: 50191 More actions · Answer 3

It is possible, by adding the json path for the true / false entry, giving you a second column in the results (or to insert into your table)

See this: https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16#path

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 4

You can use OPENJSON in SQL Server to parse the JSON string and extract the required fields dynamically.

Solution

DECLARE @json NVARCHAR(MAX) = 
'[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false},
  {"ChannelName":"LOT4 - Video","ChannelRecordId":"46c49edf-9aa1-ec11-b85f-00155d640f76","Removed":true}]'

SELECT [ChannelRecordId],
       [Removed]
  FROM OPENJSON(@json)
  WITH (ChannelRecordId NVARCHAR(50) '$.ChannelRecordId', Removed BIT '$.Removed')
;

Explanation

OPENJSON(@json) allows you to parse the JSON array dynamically.

The WITH clause extracts specific properties: ChannelRecordId (GUID) and Removed (Boolean).

This will dynamically create records based on the number of occurrences.

Output

Screenshot 2025-03-05 171231

Phil Parkin SSC Guru Points: 247159 More actions · Answer 5

carlmeads1975 wrote:

Hi, thanks for replying. its 50% of the way there. splits out my uniqueidentifier perfectly.
I however, also need the false or true part that relates to each identifier, so in the instance of the first channelrecordid it would be false.
'[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}
Is this possible?
Thanks in advance
Carl.

To avoid further confusion and follow-ups, can I suggest that you show the output you are expecting/hoping for, based on the sample data you have provided?

carlmeads1975 SSC Enthusiast Points: 116 More actions · Answer 6

That is truly fantastic and going to save me so many headaches,

one last question I promise, can I turn this into a function where I pass it the field from my db, I tried the following for the part without the removed bit and I got an error

ALTER function [dbo].[fn_IRDecisionRoute] (@code varchar)

Returns uniqueidentifier

BEGIN

DECLARE @channelrecordid varchar

set @channelrecordid = (

--SELECT ISJSON(@code)

SELECT

JSON_VALUE(value, '$.ChannelRecordId') AS ChannelRecordId

FROM OPENJSON(@code) AS oj

)

return @channelrecordid

end

GO

carlmeads1975 SSC Enthusiast Points: 116 More actions · Answer 7

Ive also tried

SELECT top 1000 [cp_initialreviewId]

,[cp_AssessmentChannelOptions]

,(SELECT [ChannelName]

FROM OPENJSON(cp_AssessmentChannelOptions)

WITH (ChannelName NVARCHAR(50) '$.ChannelName'))

,(SELECT [Removed]

FROM OPENJSON(cp_AssessmentChannelOptions)

WITH (Removed BIT '$.Removed'))

FROM [PIP_MSCRM_MI_DB].[dbo].[cp_initialreviewBase]

But I get the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any ideas?

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 8

carlmeads1975 wrote:

Ive also tried
SELECT top 1000 [cp_initialreviewId] ,[cp_AssessmentChannelOptions] ,(SELECT [ChannelName] FROM OPENJSON(cp_AssessmentChannelOptions) WITH (ChannelName NVARCHAR(50) '$.ChannelName')) ,(SELECT [Removed] FROM OPENJSON(cp_AssessmentChannelOptions) WITH (Removed BIT '$.Removed'))
FROM [PIP_MSCRM_MI_DB].[dbo].[cp_initialreviewBase]
But I get the error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas?

Instead of using subqueries, use CROSS APPLY to extract multiple values per row dynamically.

SELECT TOP 1000 
    ir.[cp_initialreviewId],
    j.[ChannelName],
    j.[Removed]
FROM [PIP_MSCRM_MI_DB].[dbo].[cp_initialreviewBase] ir
CROSS APPLY OPENJSON(ir.cp_AssessmentChannelOptions)
WITH (
    ChannelName NVARCHAR(50) '$.ChannelName',
    Removed BIT '$.Removed'
) j;

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 9

.

This reply was modified 3 months, 2 weeks ago by Jonathan AC Roberts. Reason: duplicate

carlmeads1975 SSC Enthusiast Points: 116 More actions · Answer 10

carlmeads1975

SSC Enthusiast

Points: 116

March 6, 2025 at 7:25 am

#4552341

Thank You - you are all awesome

Splitting Text based on certain phrases in string

Cookies on SQLServerCentral