?
Solved

MS SQL 7 , counting records in a delimited string

Posted on 2005-03-21
2
Medium Priority
?
728 Views
Last Modified: 2008-02-01
Hi all, I have a MS SQL table that contains data in this format-

column name: demographicAnswers

  1-A;2-A,B;3-A;4-A;5-B;6-A;7-A,B,C,D,E,F  

each number represents a question on a form, the following letter is there multiple choice answer. I have been away from the data reporting project for some time and I have unfortunalty drawn a complete blank on how to query this to give me the total count or break out of how many answered "A" to ques 1, etc. I used to use a SQL select string to pull the data and now my new tech/app writer says that it CANT be done and i swear I used to do it. If anyone could help out that would be great.
0
Comment
Question by:moneygrip
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Expert Comment

by:rherguth
ID: 13596072
I use up to three functions to do this sort of thing in SQL Server 2000:
fwt_GetWordCount gives a count of "words" using the delimter of choice.  Initally, that would be a semicolon in your text.
fwt_GetWordByNumber grabs a particular "word" using your delimiter
fwt_StringToRowsWRowNums could also be useful if you wanted to use demographicAnswers as a derived table to do a join

CREATE  FUNCTION fwt_GetWordCount
      (
      @Sentence VARCHAR(4096)
      , @WordDelimiter CHAR(1) = ' '
      )
RETURNS INT
AS
BEGIN
DECLARE @WordCount INT, @FoundPos INT, @EOF TINYINT, @StartPos INT
SELECT @EOF = 0, @StartPos = 1, @WordCount = 0

IF IsNull(@Sentence, '') > ''
BEGIN
      SET @WordCount = 1
      WHILE (@EOF = 0)
      BEGIN
            SET @FoundPos = CHARINDEX(@WordDelimiter, @Sentence, @StartPos)
            IF @FoundPos > 0
                  SELECT @StartPos = @FoundPos + 1, @WordCount = @WordCount + 1
            ELSE
                  SET @EOF = 1
                  
      END
END

RETURN @WordCount
END


/*
Purpose:
      Grabs the Nth word in a given string using the given delimiter.
Note:
      This function can be used for truncating scale in a numeric converted to a string:
      dbo.fwt_GetWordByNumber('98.001018', '.', 1) returns '98'
*/
CREATE  FUNCTION fwt_GetWordByNumber
      (
      @Sentence VARCHAR(255)
      , @WordDelimiter CHAR(1) = ' '
      , @WordNumber TINYINT
      )
RETURNS varchar(255)
AS
BEGIN
      DECLARE @start TINYINT, @end TINYINT, @loopcnt TINYINT
      SET @end = 0
      SET @loopcnt = 0
      SET @WordDelimiter = SUBSTRING(@WordDelimiter, 1, 1)
      
      --      loop to specific token
      WHILE (@loopcnt < @WordNumber)
      BEGIN
            SET @start = @end + 1
            SET @loopcnt = @loopcnt + 1
            SET @end = CHARINDEX(@WordDelimiter, @Sentence+@WordDelimiter, @start)
            IF @end = 0 BREAK
      END
      
      IF @end = 0
            SET @Sentence = null
      ELSE
            SET @Sentence = SUBSTRING(@Sentence, @start, @end-@start)
      
RETURN @Sentence
END

/*
Purpose:
      Returns a table from a delimited string with row numbers.
Type:
      Rowset Return
Prototype:
      SELECT * FROM fwt_StringToRowsWRowNums('73,84,90')
*/
CREATE FUNCTION fwt_StringToRowsWRowNums
      (
      @DString VARCHAR(4096)
      , @Delimiter VARCHAR(10) = ','
      )
RETURNS @ReturnVal Table ([RowNum] SMALLINT, [Col] VARCHAR(255))
AS  
BEGIN

DECLARE @iCount INT
SET @iCount = 1

-- Remove spaces unless a space is the delimiter
IF @Delimiter != ' '
BEGIN
      SET @DString = REPLACE(@DString, ' ' + @Delimiter, @Delimiter)
      SET @Delimiter = REPLACE(@Delimiter, ' ', '')
END

DECLARE @FoundLocation INT, @SaveLocation INT, @StringLen INT
SET @SaveLocation = 0
SET @StringLen = LEN(@DString)

WHILE @SaveLocation < @StringLen
BEGIN
      SET @FoundLocation = CHARINDEX(@Delimiter, @DString, @SaveLocation)
      IF @FoundLocation = 0 SET @FoundLocation = @StringLen + 1
      INSERT INTO @ReturnVal SELECT @iCount, RTRIM(LTRIM(SUBSTRING(@DString, @SaveLocation, @FoundLocation - @SaveLocation)))
      SET @SaveLocation = @FoundLocation + 1
      SET @iCount = @iCount + 1
END
RETURN
END

0
 
LVL 15

Accepted Solution

by:
mcmonap earned 2000 total points
ID: 13596286
Hi  moneygrip,

This will drop the question/answers pairs into a table variable, this could just as easily be a temp table, it could also include a survey id to identify which q/a's came from which survey:

DROP TABLE table3
GO
CREATE TABLE table3 (demographicAnswers VARCHAR(8000))
GO
INSERT INTO table3 VALUES ('1-A;2-A,B;3-A;4-A;5-B;6-A;7-A,B,C,D,E,F')
GO
SET NOCOUNT ON

DECLARE @tblForm TABLE (Question INT, Answer VARCHAR(30))
DECLARE @startpos INT, @endpos INT
DECLARE @answer VARCHAR(8000)

DECLARE cForms CURSOR FAST_FORWARD
FOR
SELECT demographicAnswers FROM table3

OPEN cForms
FETCH NEXT FROM cForms INTO @answer
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @startpos = 1
      WHILE @startpos < LEN(@answer)
      BEGIN
            --PRINT @startpos
            IF CHARINDEX(';',@answer,@startpos) <> 0
            BEGIN
                  INSERT INTO @tblForm (Question, Answer)
                  VALUES (LEFT(SUBSTRING(@answer,@startpos,CHARINDEX(';',@answer,@startpos)-@startpos),1)
                        , SUBSTRING(SUBSTRING(@answer,@startpos,CHARINDEX(';',@answer,@startpos)-@startpos),3,8000))
                  SET @startpos = CHARINDEX(';',@answer,@startpos)+1
            END
            ELSE
            BEGIN
                  PRINT SUBSTRING(@answer,@startpos,8000)
                  INSERT INTO @tblForm (Question, Answer)
                  VALUES (LEFT(SUBSTRING(@answer,@startpos,8000),1)
                        , SUBSTRING(SUBSTRING(@answer,@startpos,8000),3,8000))

                  SET @startpos = LEN(@answer)
            END

      END
      FETCH NEXT FROM cForms INTO @answer
END
CLOSE cForms
DEALLOCATE cForms
SET NOCOUNT OFF

SELECT * FROM @tblForm
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question