Introduction
In this article we will learn about how to split a string in comma separated value from a specified range. Split string using Sub-string function and give a space after every comma.Previous Updates
In previous articles we have learnt Generate QR code for text also Read the QR code image and Configure Setup(Installer) project in VS2015. If Else and Case statement , While Loop . What is Cursor in sql and use of cursor. Difference between Row_Number(), Rank(), Rank_Density() with example.
Practice
In SQL server many times we need split the given string into comma separated string. We can perform this task in many different different ways but here i am sharing the best and very convenient way. You can split your string from where you want , you are able to split whole string from a given no of period . Here is my SQL code for this :
|
DECLARE @BreakCount INT =5
DECLARE @STRINGVALUE VARCHAR(MAX) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ12345678900987654321ABCDEFGHIJ'
DECLARE @OutputValue VARCHAR(MAX)= ''
DECLARE @OutputValue1 VARCHAR(MAX)= NULL
DECLARE @LenOut INT = 1
DECLARE @LenOut1 INT = 1
DECLARE @Len INT ;
SELECT @Len = LEN(@StringVALUE)
SELECT @LenOut = 1
WHILE @LenOut1 !=0
BEGIN
SELECT @OutputValue = SUBSTRING(@STRINGVALUE, @LenOut, @BreakCount)
SELECT @LenOut1 = LEN(@OutputValue)
IF (@LenOut1>0)
BEGIN
IF @OutputValue1 IS NULL
BEGIN
select @OutputValue1 = @OutputValue + ', '
SELECT @LenOut = @LenOut + LEN(@OutputValue)
END
ELSE
BEGIN
select @OutputValue1 = @OutputValue1 + @OutputValue + ', '
SELECT @LenOut = @LenOut + LEN(@OutputValue)
END
END
END
SELECT @OutputValue1 AS RESULT
|
Here BreakCount variable is for where you want to break the given string. I set the BreakCount =5 means it will break the sting after every five words and add comma and space then add new string.
Here is the output in Result window for above SQL statement :
If you set the BreakCount to 1 then the output look like this:
