Parsing Question

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  February 25, 2004 at 11:18 am

  #161161

  I have an assessment table in which answers to various question have been entered into a sql 7.0 table with numeric values ( true = 1, false = 0 ). However, some fields contain results of multiple choice or range values in the form of 0~0~0~1~0~0~0 where the 1 would be the equivalnet of an answer "midly agree."  Is there any help in parsing these fields so that I can derive a single value which I can then, in turn, use in a statistical report of the survey?

• gljjr

  SSChampion

  Points: 10080

  More actions

  February 25, 2004 at 11:48 am

  #495638

  Hmmm... Upgrade to SQL2K so you can use User Defined Functions?

   

  You can take this function and modify it to suit your needs.

   

  CREATE FUNCTION dbo.f_SplitString

   (

   @strVal nvarchar(4000)

   , @strDelimiter nvarchar(1)

  &nbsp 

  RETURNS @tblSplitString TABLE (SplitString nvarchar(260) )

  AS 

  BEGIN

  DECLARE @intPos int

  DECLARE @intStart int

  DECLARE @intLen int

  SET @intStart = 0

  SET @intPos = 1

  SELECT @strVal = LTRIM(RTRIM(@strVal))

  IF (@strDelimiter IS NULL)

  BEGIN

   SET @intLen = LEN(@strVal)

   WHILE @intPos <= @intLen

   BEGIN

    INSERT @tblSplitString (SplitString)

      VALUES (SUBSTRING(@strVal, @intPos, 1 ))

    SET @intPos = @intPos + 1

   END

    

   RETURN

  END

  WHILE @intPos > 0

  BEGIN

   SET @intPos = CHARINDEX(@strDelimiter, @strVal, @intStart)

   IF @intPos != 0

    SET @intLen = (@intPos - @intStart)

   ELSE

    SET @intLen = LEN(@strVal) - @intStart + 1

    

   INSERT @tblSplitString (SplitString)

      VALUES (SUBSTRING(@strVal, @intStart, @intLen ))

   

   SET @intStart = @intPos + 1

  END

   RETURN

  END

  
  
  
  Gary Johnson
  Microsoft Natural Language Group
  DBA, Sr. DB Engineer

  This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 25, 2004 at 12:01 pm

  #495642

  You may have to give a little more detail but

  assuming the value on the right is "the True" you can ponderate by position of the '1' and in that case

  This may be enough

  DECLARE @STR as varchar(100)

  SET @STR = '0~0~0~1~0~0~0'

  SELECT 1.0 * charindex('1',Replace(@str,'~',''),1) / Len(Replace(@str,'~','')) as val

  
  * Noel

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  February 26, 2004 at 7:16 am

  #495821

  SELECT CHARINDEX('1','~'+@str)/2

  Far away is close at hand in the images of elsewhere.
  Anon.