select convert from varchar to decimal

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 2000 Forums

SQL Server Development (2000)

select convert from varchar to decimal

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-23 : 08:18:46

Mike writes "Hi,

Can someone please help me with the following problems. First of all, I want to be able to trim the domain name to keep only the domain_name.com. For example, I have the following url in my database:
0.dom.atlas.com
atlas.com
www.atlas.com
... but wanted to be able to just keep the "atlas.com" so I can group correctly group it.

Secondly, all columns got converted to varchar, 255 when imported the text delimited data to the database. This "gross" field contains 15 decimals (i.e. 0.093497950431138) and I need to:
1) Convert from varchar to 2 decimals or x.xx.
2) Round up the decimal so I can sum up the value.

I used the following select statements but it didn't work:
select convert(decimal,gross,2)
select *,SUM(tty),sum(household),sum(gross) FROM Sales GROUP BY url

Here are the sample file in my database:
url dob tty household gross
-----------------------------------------------------------------
atlas.com 1976-04-09 1 0 0.093497950431138
atlas.com 1976-07-18 1 0 0.045099517387864
0.dom.atlas.com 1976-09-04 2 0 0.063975537558869
atlas.com 1976-02-28 1 1 0.150120775703
atlas.com 1976-05-13 23 4 0
dom.atlas.com 1976-12-01 7 0 0
www.abc.net 1976-04-09 1 0 0
0579www.gem.com 1956-09-12 1 0 0
www.gem.com 1956-10-09 1 0 0.150120775703
www.lah.com 1962-01-19 1 3 0
www.lah.com 1962-01-20 1 0 0.063975537558869

The result should look like the following:
url dob tty household gross
-------------------------------------------------------------
atlas.com 35 5 0.35
abc.net 1 0 0
gem.com 2 0 0.15
lah.com 2 3 0.06

*atlas.com ==> GROSS column rounded up to 0.35 from 0.352693781080871 or total of atlas.com
*The URL field, abc.net (trim or remove www. or 0.dom., etc...)
*The DoB field is not so important but would like to have it displayed as well. Most important is to trim and group "url", sum each of the tty, household and inc separately and inc needs to be rounded up to the nearest .xx decimals.

Thank you so much in advance!

Mike"

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-23 : 16:30:45

In broad strokes...

A lot will depend on the actual business rules you have for the incoming string. Will there always be a specific number of embedded periods? Is each string a URL? etc.

You can use the CHARINDEX function to locate the position of the dot separator(s). If it makes it easier the REVERSE function can be used.

1) REVERSE the string
2) Use CHARINDEX to find the first (actually the last period)
3) Use CHARINDEX to find the second period, starting at the first period + 1
4) Use SUBSTRING to pluck off that portion of the total string
5) REVERSE the output of SUBSTRING

Combine are of that logic into a single SELECT.

Et Viola

HTH

=================================================================
Too many parents make life hard for their children by trying, too zealously, to make it easy for them. -Johann Wolfgang von Goethe, poet, dramatist, novelist, and philosopher (1749-1832)

lionhearthg
Starting Member

1 Post

Posted - 2005-02-24 : 16:46:29

Hi Steve,

Thanks for the input. Actually, I just realized yesterday that few of the url records contain garbage chars after the "domain_name.com". Below are example of the data in the "url" field:

atlas.com
www.atlas.com
0029.atlas.com
www.atlas.com.yhoo.co.uk
atlas.com.
atlas

I know it's weird to have such combination. I can have the file imported to Excel then use import with an "other -> ." option to separate then combine the field(s). But the problem is that my file is greater than 220,000 records and excel can only take upto (I believe) 66,000 rows.

Btw, do you have any other tips/suggestions for my other questions?

Below are my sql statements:
select url,sum(tty),sum(results) from sample group by url
select convert(decimal(10,2), gross) as revenue from sample

I also changed my table data type from all varchar to the following:
url: varchar
dob: varchar
tty: numeric
results: numeric
gross: varchar

I don't know how to combine the above select statements in order to select *, sum(tty), sum(results), sum(gross) and group by url.

Thanks very much. Have a great day!

Mike

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-25 : 18:27:55

If you need to perform a good deal of string manipulation, you might want to consider moving the raw data to the application and let it be massaged there. SQL isn't really a very good string handler.

As to your questions:

There is a ROUND function that may be of assistance to you. See BOL for details.

In order to combine both raw and aggregated data you can use subqueries in the SELECT clause (this is slow). You can also join the table to a correlated subquery of the aggragated values. Use the GROUP BY column(s) to join them.

e.g.

SELECT a.url, a.Col2, b.SumTTy, b.SumResults
from MyTable a
join (select url, sum(tty) SumTTy, sum(Results)
from MyTable a1
group by url) b
on a.url = b.url

Good luck.

HTH

=================================================================
Don't say you don't have enough time. You have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein.
-H. Jackson Brown, Jr., writer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources