CODEWITHSUNDEEP
mysqlsql-server-2008extracttrim
CSharpProgrammer
CSharpProgrammer

Reputation: 29

Trimming in SQL Management Studio 2008

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)

Upvotes: 0

Views: 512

Answers (3)

Andomar
Andomar

Reputation: 238246

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.

Upvotes: 1

Mark
Mark

Reputation: 108567

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;

Upvotes: 0

JanW
JanW

Reputation: 1849

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

Upvotes: 0

Related Questions