CODEWITHSUNDEEP
sqljsp
user2706
user2706

Reputation: 267

How to split comma separated string in sql

Split only after comma 3 times appear in sql. I have string that looks like this

"abc,123,855,jkl,ddd,rrr,sss,999,777,kkk,jkl,ddd,rrr,sss,999"

getting this from db. What I want returned is a string[] that was split after every 3rd comma, so it would look like this:

abc,123,855,
jkl,ddd,rrr,
sss,999,777,
kkk,jkl,ddd,
rrr,sss,999

Need this field to display in my JSP page, currently it taking more space of the table row. Would really appreciate any help I can get!

Upvotes: 0

Views: 643

Answers (3)

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

You can accomplish this far more easily in JSP using JSTL. In fact, since your actual need is just of display, you don't even need to create an array. Here is how to do it using the <c:forTokens> JSTL tag.

<c:set var="sqlString" value="abc,123,855,jkl,ddd,rrr,sss,999,777,kkk" />

<c:forTokens var="token" varStatus="tokenCounter" items="${sqlString}" delims=",">
    <c:out value="${token}" />,
    <c:if test="${tokenCounter.count % 3 == 0}">
        <br />
    </c:if>
</c:forTokens>

Upvotes: 0

milan minarovic
milan minarovic

Reputation: 123

Solution for MS SQL. Assuming that your string with commas is in column in a table and there could be more rows with more different strings. Also assuming that last string remaining after parsing could have less than 3 commas.

Using two loops, First loop is checking if there are still rows to process. Second inner loop is checking if there is still remaining string with commas to process.

Original table with comma string column TableWithCommaStrings Final table in this case only temp #Temp_3, but you can insert it anywhere.

IF OBJECT_ID('tempdb..#Temp_1') IS NOT NULL DROP TABLE #Temp_1
SELECT * 
  INTO #Temp_1 
  FROM TableWithCommaStrings 
;

DECLARE @RowCount AS INT = 1;
WHILE @RowCount >= 1 
BEGIN

    IF OBJECT_ID('tempdb..#Temp_2') IS NOT NULL DROP TABLE #Temp_2
    SELECT TOP 1 * /* Pick Only First line */
      INTO #Temp_2
      FROM #Temp_1;

    WITH AA AS (SELECT TOP 1 * FROM #Temp_1 ) /* Delete that first line from Temp1*/
    DELETE FROM AA;

        /* Parse Column till last comma character remain there*/
        DECLARE @CommaCharCount AS INT = 3;
        WHILE @CommaCharCount >= 3 
        BEGIN
            IF OBJECT_ID('tempdb..#Temp_3') IS NULL CREATE TABLE #Temp_3 (YourStringColumn VARCHAR(MAX));
            INSERT INTO #Temp_3 /* Insert substring into temp3 based on Third Comman from beginning*/
            SELECT substring(AA.YourStringColumn,1,(CharIndex(',',AA.YourStringColumn,Charindex(',',AA.YourStringColumn,CharIndex(',',AA.YourStringColumn)+1) +1))-1)
            FROM #Temp_2 AS AA

            UPDATE BB /*Remove part of string which has been procssed already in step above*/
            SET BB.YourStringColumn = substring(YourStringColumn,((CharIndex(',',YourStringColumn,Charindex(',',YourStringColumn,CharIndex(',',YourStringColumn)+1) +1)+1)+1),LEN(YourStringColumn))
            FROM #Temp_2 AS BB

            /* Set comma counter */
            SELECT @CommaCharCount= LEN(YourStringColumn) - LEN(REPLACE(YourStringColumn, ',', '')) FROM #Temp_2

        END

IF (SELECT LEN(YourStringColumn) FROM #Temp_2) > 0  
    INSERT INTO #Temp_3 /* Last remaining Characters */
    SELECT YourStringColumn
    FROM #Temp_2

SELECT @RowCount = COUNT(*) FROM #Temp_1;

END

Upvotes: 0

Sohail Ahmad
Sohail Ahmad

Reputation: 13

Here is the complete example.

DECLARE @valueList varchar(8000)
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000)
SET @valueList = 'Pakistan,UAE,USA,UK,'

set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
    SELECT @pos, @len, @value

    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

Upvotes: 1

Related Questions