Need some CHARINDEX String help

Bobby P 221 Reputation points
2023-05-26T14:39:35.4+00:00

I have this String...

North Carolina (01) >> State (03) >> State Funded (09) >> AMI (21)

I need to get the "State Funded" and its corresponding "09"

I have been playing with SUBSTRING and RIGHT and CHARINDEX to try and get those pieces out of the string but I cannot for the life of me get the syntax correct.

I have been taking it a piece at a time and will continue while hoping for a reply to my post.

Thanks in advance for your review and am hopeful for a reply.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,323 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 106.5K Reputation points
    2023-05-26T21:38:07.6266667+00:00
    SELECT substring(value, charindex('(', value) + 1, charindex(')', value) - charindex('(', value) - 1)
    FROM   string_split('North Carolina (01) >> State (03) >> State Funded (09) >> AMI (21)', '>')
    WHERE  value LIKE '% State Funded %'
    

    Although I predict that there are strings that do fit this pattern. This kind of stuff is not very fun to do directly in SQL Server, unless you want to write a CLR function to wrap the RegEx class.

    0 comments No comments

  2. Yitzhak Khabinsky 25,726 Reputation points
    2023-05-28T03:20:32.3966667+00:00

    Hi @Bobby P,

    Your data resembles JSON key/value pairs. That's why we can use tokenization via JSON instead of string parsing.

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(MAX));
    INSERT @tbl (tokens) VALUES
    ('North Carolina (01) >> State (03) >> State Funded (09) >> AMI (21)');
    -- DDL and sample data population, end
    
    SELECT ID, [key], [value]
    FROM @tbl
    CROSS APPLY	OPENJSON('{"' + TRIM(')' FROM REPLACE(REPLACE(tokens, ' (', '": "')
    	, ') >> ', '", "')) + '"}')
    --WHERE [key] = 'State Funded';	-- uncomment this line when you are ready
    
    
    
    0 comments No comments

  3. LiHongMSFT-4306 25,651 Reputation points
    2023-05-29T01:42:56.1666667+00:00

    Hi @Bobby P

    If the string format is always four tokens concatenated with '>>', then you might have a try on PARSENAME function.

    Check this:

    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, String VARCHAR(MAX));
    INSERT @tbl (String) VALUES
    ('North Carolina (01)>>State (03)>>State Funded (09)>>AMI (21)');
    
    SELECT PARSENAME(REPLACE(String,'>>','.'),2) AS Result
    FROM @tbl
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments