Tricky Select query

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  September 22, 2006 at 7:29 am

  #69246

  Guys

  I have following column data varchar(20) with 100k records in it.

  data

  ______

  (1,2,3)

  (1,2)

  (1,4,6)

  .

  .

  .

  .

  I want a query which calculates the sum of the column data in the above case

  sum = 1+2+3+1+2+1+4+6.....

  earlier I used something like this but it doesnt work since the length the string is not always the same and also the no of rows table is dynamically growing.

  I even tried to use replace function but somehow it didnt fall into place

  select cast (substring ('1,2', 1, 1) as int)+cast(substring ('1,2', 3, 1) as int)

  Any suggestions/inputs would help

  Thanks

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  September 22, 2006 at 8:02 am

  #661984

  Not the most elegant solution, but it works...

  John

  --Data

  create

  table #John (data varchar(20), result int)

  insert

  into #John (data) values ('(1,2,3)')

  insert

  into #John (data) values ('(1,2)')

  insert

  into #John (data) values ('(1,4,6)')

  --Generate query

  set

  nocount on

  select

  'update #John set result = ' + replace(data, ',', '+')

  + ' where data = ''' + data + ''''

  from

  #John

  --Executing the above will generate the three lines below...

  update

  #John set result = (1+2+3) where data = '(1,2,3)'

  update

  #John set result = (1+2) where data = '(1,2)'

  update

  #John set result = (1+4+6) where data = '(1,4,6)'

  --Execute this to do the calculations

  --Then to see the results:

  select

  * from #John

• Ram Ram

  Ten Centuries

  Points: 1079

  More actions

  September 22, 2006 at 8:05 am

  #661986

  You may try this out

  SET NOCOUNT ON

  DECLARE @TBL TABLE (C1 VARCHAR(20))

  INSERT INTO @TBL VALUES('1,2,3')

  INSERT INTO @TBL VALUES('1,4')

  INSERT INTO @TBL VALUES('1,6')

  SELECT SUM(

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,1,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,1,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,2,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,2,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,3,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,3,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,4,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,4,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,5,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,5,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,6,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,6,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,7,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,7,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,8,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,8,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,9,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,9,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,10,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,10,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,11,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,11,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,12,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,12,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,13,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,13,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,14,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,14,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,15,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,15,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,16,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,16,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,17,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,17,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,18,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,18,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,19,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,19,1) AS INT) ELSE 0 END +

   CASE ISNUMERIC(REPLACE(SUBSTRING(C1,20,1),',','A')) WHEN 1 THEN CAST(SUBSTRING(C1,20,1) AS INT) ELSE 0 END

  &nbsp AS TotalSum

  FROM @TBL

  Ram

   

   

• Vishal Gamji

  SSC Enthusiast

  Points: 149

  More actions

  September 22, 2006 at 8:57 am

  #662000

  This might be kinda similar to one of the responses above. Cursor usage is bad ... but works..

  ---------------------------------------------------

  SET NOCOUNT ON

  DECLARE @val varchar(25)

  CREATE TABLE Temp(ID int PRIMARY KEY IDENTITY(1,1), Value varchar(25))

  CREATE TABLE #Temp(ID int PRIMARY KEY IDENTITY(1,1), Value varchar(25))

  INSERT INTO Temp(Value) VALUES('1,2,3')

  INSERT INTO Temp(Value) VALUES('4,5')

  INSERT INTO Temp(Value) VALUES('6,7,8,9')

  DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR

  SELECT Value FROM Temp

  OPEN myCursor

  WHILE 1 = 1

  BEGIN

  FETCH NEXT FROM myCursor INTO

  @val

  IF @@FETCH_STATUS 0

  BREAK

  SET @val = REPLACE(@Val,',','+')

  PRINT 'SELECT ' + @val --Replace this with your query

  END

  CLOSE myCursor

  DEALLOCATE myCursor

  --SELECT * FROM Temp

  DROP TABLE #Temp

  DROP TABLE Temp

  ------------------------------------------------------------

  --Copy the text (print statements) from result set to clipboard and paste in Query Analyzer...

  --and execute.