sql select sum(left?) query

[Autosys]

Hi there

I was wondering how I would do the below in SQL 2005.

I have a column A in table TBL. Column A has the following values:

4h
7h
24h
100h

I would like to work out the total number of hours in this column with a select statement. So my initial thoughts were somthing like this but I appreciate my left won't work properly in for the 24 and the 100 values.

select sum(left(A,1) from TBL ..

I would therefore like to chop the "h" before doing my sum any advice would be great thanks!

S

 

[Ach005ki]

Have you tried something along the lines of this to start with?

The last select is the summing one, the one before this simply checks that the CAST operation has worked.

create table #mhtemp2 (A char(6) )
insert into #mhtemp2 select '4h' union all select '12h' union all select '09h'union all select '100h'
select * from #mhtemp2


SELECT SUBSTRING(A,1,LEN(RTRIM(A))-1)
     , CAST(SUBSTRING(A,1,LEN(RTRIM(A))-1) as SmallInt)
	 , SUM(CAST(SUBSTRING(A,1,LEN(RTRIM(A))-1) as SmallInt)) 
  FROM #mhtemp2
 GROUP 
    BY SUBSTRING(A,1,LEN(RTRIM(A))-1)
     , CAST(SUBSTRING(A,1,LEN(RTRIM(A))-1) as SmallInt)
	 
SELECT SUM(CAST(SUBSTRING(A,1,LEN(RTRIM(A))-1) as SmallInt)) 
  FROM #mhtemp2

Mark, somewhere near Blackburn Lancs!

 

[djj55]

How about something like:

select sum(cast(replace(A,'h','') as int))

djj

 

[Ach005ki]

I don't know why I opted for a SUBSTRING rather than a LEFT, but the LEFT would be something like:

SELECT SUM(CAST(LEFT(A,LEN(RTRIM(A))-1) as SmallInt))
FROM #mhtemp2

BTW, I like yours for simplicity, djj - I hadn't realised that a CAST would trim trailing blank / space characters before converting the expression to a numeric data type!



Mark, somewhere near Blackburn Lancs!

 

[Autosys]

cool thanks for all the help arch005..

have to go with djj's for simplicity thanks!