Trimming in SQL Management Studio 2008

Question

Hi, I'm trying to trim some info from a table. The column has a number and then a word (e.g. 5 Apples). I just need the number so that I can sum the total of apples. I can't use count because I need to go by the value (e.g. 5 Apples for one, 3 Apples for the other) and count will just return that there are 2 entries and not pull the 5 and 3.

I've tried using SUM but it doesn't work either.

Can anyone help (Or point me to a tutorial) that will explain how I can trim/extract info from the columns value? I've been looking and have only been able to find how to trim spaces (Which would be nice if I could trim everything after spaces too, then I'd just be able to trim away after the number)

Any help is greatly appreciated! Thanks

Answer 1

Sounds like you need to SubString with CharIndex, Cast/Convert the resulting 'number' to an int and then use SUM.

Assuming your 'number' is always followed by 'Apples' (untested):

SELECT SUM(CONVERT(int,SUBSTRING(column_name,0,CharIndex(' Apple',column_name)-1))) as numApples FROM table_name;

Answer 2

A combination of sum, cast, substring, charindex will do the work i think:

Find the first occurence of whitespace, take the substring of the column until the first whitespace position, cast it as an integer and sum it up.

SELECT SUM(CAST(SUBSTRING(yourColumn, 1, CHARINDEX(' ', yourColumn) -1) AS INT)) 
FROM yourTable

Edit: first char is 1 in substring

Answer 3

You can use patindex to search for the first non-digit, and then substring to get only the numeric part of the string:

declare @fruit table (id int identity, description varchar(50))
insert @fruit (description) select '3 apples'
union all select '10 apples'
union all select '12 apples'

select  sum(cast(substring(description, 1, FirstNonDigit) as int))
from    (
        select  patindex('%[^0-9]%', description) FirstNonDigit
        ,       description
        from    @fruit
        ) as SubQuery
where   FirstNonDigit > 0

This prints 25.

Having said that, a good table design would put the number and description in two different columns.