Click here to Skip to main content
16,019,618 members
Please Sign up or sign in to vote.
1.60/5 (2 votes)
How Can I get substring between commas?


eg: Thursday,May,05,2013

Friday,May,05,2013


how can i get the month 'May' from this.
Posted
Updated 13-May-13 0:25am
v2
Comments
Maciej Los 13-May-13 6:26am    
What have you done till now?

Try this:
SQL
DECLARE @tbl TABLE (myText NVARCHAR(30))

INSERT INTO @tbl(myText)
VALUES('Friday,May,05,2013')

;WITH cte AS
(
	SELECT LEFT(myText, CHARINDEX(',', myText)-1) AS Word, RIGHT(myText, LEN(myText) - CHARINDEX(',', myText)) AS Remainder
	FROM @tbl
	WHERE CHARINDEX(',', myText)>0
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS Word, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM cte
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder AS Word, NULL AS Remainder
	FROM cte
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT *
FROM cte
WHERE Word = 'May'


More about Common Table Expressions[^].
 
Share this answer
 
v2
Comments
gvprabu 13-May-13 7:18am    
Maciej,
Nice Work, But Some times we will give only logic then it will helps to think their own way. If we will give like this full solutions, then they will use only COPY and PAST. This is my suggestion only. But ur job is really Great. Psl continue as ur way. Thank you.
Maciej Los 13-May-13 8:26am    
Thank you for your suggestion and vote-up ;)
This does rather more than you need: Using comma separated value parameter strings in SQL IN clauses[^] - but it's doing the same task, just a bit more completely.
 
Share this answer
 
Comments
Maciej Los 13-May-13 6:40am    
+5
Have a look at this code project article[^] on the subject
 
Share this answer
 
Comments
Maciej Los 13-May-13 6:39am    
+5

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900