How to get all desired substrings in sql server

Question

1.00/5 (1 vote)

See more:

, +

How do i get all the strings in CSV format which are enclosed by $$$ symbols in sql server.

IF i have a string as

Dear $$$Name$$$ your organization is $$$Organization$$$
my output needs to be like
(Name,Organization)

there can be any number of $$$ enclosed strings in my input string and this solution is to be in SQL.

Please advice. Thanx

Posted 9-Jun-15 20:25pm

Vinay1337

Updated 9-Jun-15 22:10pm

F-ES Sitecore

v2

Add a Solution

Comments

Tomas Takac 10-Jun-15 2:38am

What did you try? Where are you stuck?

Vinay1337 10-Jun-15 2:40am

I am not even getting how to start
The idea i thought was to iterate until the string length goes zero and get the first two appearances of $$$ and get the sub string between them but don't know how to achieve that.
any algo or idea is welcome.

Maciej Los 10-Jun-15 2:54am

I don't get you...
Do you want to get text between $$$?

Vinay1337 10-Jun-15 2:58am

yes i want the text between two pairs of $$$ in the given input string
if the string is Dear $$$name$$$ then i want 'name' as the output

5 solutions

Solution 1

Check out these String Functions (Transact-SQL)[^] to construct your sql statement.
Specifically, I think you will need substring, len, and charindex.

Posted 9-Jun-15 21:18pm

Peter Leow

Solution 2

Set a variable Initial Position = to 0
Create a while loop
find the first position of your token $$$ using SubString
set the start position value
find the next instance of the token
set the end position
grab the text between start and end position
set the initial position to the end position

repeat until the end of the text!

Posted 9-Jun-15 21:21pm

Mycroft Holmes

Solution 3

Try this:

SQL

DECLARE @myText VARCHAR(255) = 'Dear $$$Name$$$ your organization is $$$Organization$$$'

DECLARE @tmp TABLE (InputText VARCHAR(255), Word VARCHAR(255))
--split text to words and insert into new table (table variable)
;WITH CTE AS
(
	SELECT @myText AS InputText, LEFT(@myText, CHARINDEX(' ', @myText) - 1) AS Word, RIGHT(@myText, LEN(@myText) - CHARINDEX(' ', @myText)) AS Remainder
	WHERE CHARINDEX(' ', @myText)>0
	UNION ALL
	SELECT InputText AS InputText, LEFT(Remainder, CHARINDEX(' ', Remainder) - 1) AS Word, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(' ', Remainder)) AS Remainder
	FROM CTE
	WHERE CHARINDEX(' ', Remainder)>0
	UNION ALL
	SELECT InputText AS InputText, Remainder AS Word, NULL AS Remainder
	FROM CTE
	WHERE CHARINDEX(' ', Remainder)=0
)
INSERT INTO @tmp (InputText, Word)
SELECT InputText, REPLACE(Word, '$$$', '') AS Word
FROM CTE
WHERE PATINDEX('$$$%$$$', Word)>0

--get comma separated text
SELECT DISTINCT InputText, STUFF((SELECT ', ' + t2.Word 
        FROM @tmp AS t2
		WHERE t1.InputText = t2.InputText
        FOR XML PATH('')), 1, 2, '') AS Cols
FROM @tmp t1

Result:

InputText	                                            Cols
Dear $$$Name$$$ your organization is $$$Organization$$$	Name, Organization

Posted 9-Jun-15 21:29pm

Maciej Los

Comments

Vinay1337 10-Jun-15 4:16am

This works fine but fails when there are spaces in text between $$$ such as
Dear $$$First Name$$$ it fails to get this

Maciej Los 10-Jun-15 4:49am

See solution5 ;)

Solution 4

Adapted from this link[^] - adaption was to cater for longer separator

SQL

-- Adapted from http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/

DECLARE @string VARCHAR(MAX) = 'Dear $$$Name$$$ your organization is $$$Organization$$$'
DECLARE @delimiter VARCHAR(3) = '$$$'

DECLARE @output TABLE (splitdata NVARCHAR(MAX))

DECLARE @start INT, @end INT 
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
WHILE @start < LEN(@string) + 1 BEGIN 
    IF @end = 0  
        SET @end = LEN(@string) + LEN(@delimiter)
       
    INSERT INTO @output (splitdata)  
    VALUES(SUBSTRING(@string, @start, @end - @start)) 
    SET @start = @end + LEN(@delimiter)
    SET @end = CHARINDEX(@delimiter, @string, @start)
        
END 
SELECT '!' + splitdata + '!' FROM @output

Results ('!' added to highlight that spaces are included!)

Dear !
!Name!
! your organization is !
!Organization!

Posted 9-Jun-15 22:47pm

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-06-09T22:49:00

As to analogy in solution 3, below solution provides a way to get values between a pair of $$$. Now you have to insert words into temporary table to be able to merge it into single comma separated column.

SQL

;WITH SignPairs AS
(
    SELECT @myText AS InputText, CHARINDEX('$$$', @myText) AS Pair1,
            CHARINDEX('$$$', @myText, CHARINDEX('$$$', @myText)+3) AS Pair2, 1 AS Occurence
    WHERE CHARINDEX('$$$', @myText)>0 AND CHARINDEX('$$$', @myText, CHARINDEX('$$$', @myText)+3)>0
    UNION ALL
    SELECT InputText AS InputText, CHARINDEX('$$$', InputText, Pair2+3) AS Pair1,
            CHARINDEX('$$$', InputText, CHARINDEX('$$$', InputText, Pair2+3)+3) AS Pair2, Occurence + 1 AS Occurence
    FROM SignPairs
    WHERE CHARINDEX('$$$', InputText, Pair2+3)>0 AND CHARINDEX('$$$', InputText, CHARINDEX('$$$', InputText, Pair2+3)+3)>0
)
SELECT InputText, Pair1, Pair2, Occurence, REPLACE(SUBSTRING(InputText,Pair1, Pair2 - Pair1), '$$$', '') AS Word
FROM SignPairs