• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

MS SQL 7 , counting records in a delimited string

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
moneygrip
Asked:
moneygrip
1 Solution
 
rherguthCommented:
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
 
mcmonapCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now