SQL Server 2012 split out multiple values in one varchar(1200)

Question

In a SQL Server 2012 database, I am supposed to count the number of times each 'canned' message is used for elementary students in the last school year and the current school year.

Right now I have the following T-SQL that kind of works:

USE TEST

SELECT 
    GS.Comments, COUNT(*) AS [Counts] 
FROM 
    dbo.Enrol Enrol
JOIN 
    dbo.Student Student ON Student.StudentID = Enrol.StudentID 
JOIN 
    dbo.GS GS ON GS.StudentID = Student.Studentid
              AND (GS.Comments IS NOT NULL)
              AND (GS.Comments <> '')
WHERE
    Enrol.grade IN ('KG', '01', '02', '03', '04', '05', '06')
    AND Enrol.endYear BETWEEN 2016 AND 2017 
GROUP BY
    GS.Comments
ORDER BY
    Counts DESC, GS.Comments ASC

The problem is the GS.Comments column is defined as varchar(1200). There can be one message in the column and/or there can be lots of messages in this column. Each message ends with a period and there is a space between each message.

An example of multiple messages in the one GS.Comments column would look like the following:

The student is trying hard and needs to make their time more efficiently. This student is good at math. This student turns in their assignments on time. This student seems to enjoy school.

An example of when one messages is in the one GS.Comments column would look like the following:

This student seems to enjoy school.

Thus would show me the T-SQL logic that I can use when the GS.Comments column contains multiple messages and/or just one message so that I can count the number of times each unique message has been used?

score 0 · Answer 1 · edited May 23 '17 at 11:47

0

You can split your column on periods using the following link. then a simple group by on the newly formed column should let you count it.

Splitting delimited values in a SQL column into multiple rows

edited May 23 '17 at 11:47

Community

1
1

answered May 19 '17 at 21:39

Brian Heward

524
4
14

can you give me a more specific example of using a split function? – user1816979 May 21 '17 at 02:14

score 0 · Answer 2 · answered May 24 '17 at 14:42

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON

Alter proc [dbo].[StringSplitIntoRows]
(
@tbl varchar(100),---table name as parameter
@col varchar(100)---column name as parameter
)
As
Begin
--creating two temp tables

If OBJECT_ID('tempdb..#test1') is not null drop table #test1
create table #test1(tempcol varchar(200))

--inserting the table(has comma seperated string column) data into temp table 

    Declare @tempresult nvarchar(500)
    set @tempresult = 'insert into #test1(tempcol) select ' + quotename(@col) + ' from ' + quotename(@tbl)
    exec sp_executesql @tempresult

If OBJECT_ID('tempdb..#test2') is not null drop table #test2
            create table #test2(tempcol1 varchar(200))

    Declare @Name varchar(200)
    Declare @real VARCHAR(100) 
    declare split cursor for ---declared a cursor to fetch  row by row data 
            select tempcol from #test1 --temp table which has comma seperated string in column
open split 
        fetch next from split into  @Name

    while (@@FETCH_STATUS=0)
    Begin
            declare @temp int=0
            declare @result nvarchar(MAX)=''
            declare @begin int=0    

        while CHARINDEX(',',@Name,@begin) > 0
        begin
            set @temp=CHARINDEX(',',@Name,@begin)

            set @result=SUBSTRING(@Name,@begin,@temp-@begin)                
            set @begin=@temp+1
            insert into #test2(tempcol1) values(@result)    
        end

        set @real = SUBSTRING(@Name,@begin,len(@Name)-abs(@temp-@begin)+1)
                insert into #test2(tempcol1) values(@real)

    fetch next from split into @Name

End 
        select  distinct tempcol1 from #test2
Close split
Deallocate split
end
GO
--execution 
exec StringSplitIntoRows 'YourTableName','ColumnName'

SQL Server 2012 split out multiple values in one varchar(1200)

2 Answers2