Need some CHARINDEX String help

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.