How to substring multiple values from string?

Question

I have string like this DECLARE @string VARCHAR(100)='"Tom"cscd"Lora"dasd"Jimmy"dy';

The result I want is the values inside the double quotes such as Tom ,Lora and Jimmy, and not include cscd,dasd or dy.

Please help.

Answer 1

Hi @Riley

If the strings are all structured as "name"xxx"name"xxxx"name"xx"name", then try this query:

DECLARE @string VARCHAR(100)='"Tom"cscd"Lora"dasd"Jimmy"dy';  
;WITH CTE AS  
(  
 SELECT 1 AS Position  
       ,SUBSTRING(@string,1,1) AS CharAtPos  
       ,CASE WHEN SUBSTRING(@string,1,1)='"' THEN 0 ELSE -1 END AS QuoteGroup  
       ,CASE WHEN SUBSTRING(@string,1,1)='"' THEN 1 ELSE 0 END AS QuoteIsOpen  
 UNION ALL  
 SELECT r.Position+1  
       ,SUBSTRING(@string,r.Position+1,1)  
       ,CASE WHEN SUBSTRING(@string,r.Position+1,1)='"' THEN CASE WHEN r.QuoteIsOpen=0 THEN r.QuoteGroup+1 ELSE r.QuoteGroup END ELSE r.QuoteGroup END AS QuoteGroup  
       ,CASE WHEN SUBSTRING(@string,r.Position+1,1)='"' THEN CASE WHEN r.QuoteIsOpen=0 THEN 1 ELSE 0 END ELSE r.QuoteIsOpen END AS QuoteIsOpen  
 FROM CTE r  
 WHERE r.Position+1<=LEN(REPLACE(@string,' ','*'))   
)  
SELECT (SELECT CharAtPos AS [*]  
        FROM CTE r2   
        WHERE r2.QuoteGroup=r.QuoteGroup AND r2.QuoteIsOpen=1 AND r2.CharAtPos<>'"'  
        ORDER BY r2.Position  
        FOR XML PATH(''),TYPE).value('.','varchar(100)')as split_string  
FROM CTE r  
WHERE r.QuoteGroup>=0  
GROUP BY QuoteGroup;  

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.

Answer 2

Hi @Riley,

It is possible to use STRING_SPLIT() function and filter based on the first character being in upper case.

As an alternative, it is possible to retrieve tokens/words based on their position, i.e. 1st, 3rd, and 5th.

DECLARE @string VARCHAR(100)='"Tom"cscd"Lora"dasd"Jimmy"dy';
SELECT *
FROM STRING_SPLIT(@string, '"')
WHERE value COLLATE Latin1_General_BIN LIKE '[A-Z]%' ;