Converting from varchar to int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Converting from varchar to int

Author

Topic

pumza.magona
Starting Member

6 Posts

Posted - 2010-07-09 : 09:45:39

Hi Guyz, need your help.

I am trying to use the Average function:

select avg(AverageSpend)
from subSubscriber

Error: Operand data type varchar is invalid for avg operator. Because the data is like this:

AverageSpend
R333.61
R195.02
R165.08
R165.33

I am have been trying to "convert" and "cast" but no avail.

I would realy appreciate your assistance.

Pushy

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-09 : 09:50:51

i guess because your trying to use some aggregate function on mathematical expression
that have some character...

A maze make you much more better

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 09:51:47

Can you show us all possible different values in AverageSpend?
Is it always one 'R' in front of the numeric value or are there other characters and so on...?

No, you're never too old to Yak'n'Roll if you're too young to die.

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-09 : 09:56:42

AverageSpend always on the front is 1 Character makes use of the function Substring..

http://www.coderecipes.net/sql-substring.aspx

after removing your character you can use now aggregate function of Average...

A maze make you much more better

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 09:58:43

If the values are ALWAYS like shown above try:
select avg(convert(decimal(12,2),right(AverageSpend,len(AverageSpend)-1))) from subSubscriber

No, you're never too old to Yak'n'Roll if you're too young to die.

pumza.magona
Starting Member

6 Posts

Posted - 2010-07-09 : 10:45:16

Hi All,

Webred: The other differenct values on Average Spend are as follows:

R1,976.72
R1,519.39
R1,357.68

Could this be the problem coz there are R1,976.72 values and R333.61.

Please help.

Pushy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 11:40:25

[code]select
AVG(
convert(decimal(12,2),
right(AverageSpend,case when patindex('%[^0-9.]%',reverse(AverageSpend))-1 >0 then patindex('%[^0-9.]%',reverse(AverageSpend))-1 else len(AverageSpend)end )
)
)as clearedAVG
from subSubscriber[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

pumza.magona
Starting Member

6 Posts

Posted - 2010-07-12 : 02:17:47

Webfred, u r a super*. It worked!!!

Thank You

Pushy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-12 : 02:39:15

welcome