Splitting Text based on certain phrases in string

  • 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.

  • 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
  • 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.

  • 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

  • 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

  • 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?


  • 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

  • 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?

  • 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;
  • .

  • Thank You - you are all awesome

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

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