moneygrip
asked on
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;
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.
column name: demographicAnswers
1-A;2-A,B;3-A;4-A;5-B;6-A;
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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('9
*/
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('
*/
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(@DSt
SET @SaveLocation = @FoundLocation + 1
SET @iCount = @iCount + 1
END
RETURN
END