Query (count) question

I am somewhat new to SQL and I have a simple question (I think)... I have a field called results that contains several numbers seperated by columns, such as 3,6,16,22 etc.

I want to write a query that answers how many of each number occurs from the range.

Example:

Select (total) results where id = 6 and results = 16

so the query would have to search within the string results for all the records retrieved and count the instances

how would I do this?

I'd do the following. First you'll need a numbers table as in

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

now create a view on your table that splits out the numbers

create view SplitResults
as
select id,
cast(substring(results,
Number,
charindex(',',
results + ',',
Number) - Number) as int) as results
from MyTable
inner join Numbers on Number between 1 and len(results) + 1
and substring(',' + results, Number, 1) = ','
GO

Finally, run a query on the view to get your results

select count(*)
from SplitResults
where id=6 and results=16

|||Thanks, I new it was simple

query

hi,
Have a problem and hope someone can help.
I have a column which consists of numbers such as
123
235
456
888
etc
How can i write a simple query to change them to
00123
00235
00456
00888
etc
Kindly advise.
Thank you very much
you can concatenate the data using a + sign.
Select '00' + cast(ColumnNameHere as varchar(255)) from tablename
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
news:47F589A4-FD90-42C0-8833-6D3D93E28C0A@.microsoft.com...
> hi,
> Have a problem and hope someone can help.
> I have a column which consists of numbers such as
> 123
> 235
> 456
> 888
> etc
> How can i write a simple query to change them to
> 00123
> 00235
> 00456
> 00888
> etc
> Kindly advise.
> Thank you very much
>
|||Thank you very much. What if in the column, there is also alphabets like
abc
rde
fgd
123
256
and I just want 00+ to apply to numbers only. What should I do? Kindly advise.
Thank you
"Warren Brunk" wrote:

> you can concatenate the data using a + sign.
> Select '00' + cast(ColumnNameHere as varchar(255)) from tablename
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "Tiffany" <Tiffany@.discussions.microsoft.com> wrote in message
> news:47F589A4-FD90-42C0-8833-6D3D93E28C0A@.microsoft.com...
>
>
|||On Wed, 25 Oct 2006 22:30:01 -0700, Tiffany wrote:

>Thank you very much. What if in the column, there is also alphabets like
>abc
>rde
>fgd
>123
>256
>and I just want 00+ to apply to numbers only. What should I do? Kindly advise.
Hi Tiffany,
Use a CASE expression:
SELECT CASE
WHEN ColumnNameHere NOT LIKE '%[^0-9]%'
THEN '00' ELSE '' END + ColumnNameHere
FROM TableNameHere;
Hugo Kornelis, SQL Server MVP
|||Hugo,
Instead of combining NOT LIKE and [^0-9], wouldn't that be easier to understand as:
SELECT CASE
WHEN ColumnNameHere LIKE '%[0-9]%' THEN '00'
ELSE ''
END + ColumnNameHere
FROM TableNameHere;
(I've always hated double negatives...)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message news:fq52k2hgo2p6nfpg4jq67bvkml3r3hnhhn@.4ax.com...
> On Wed, 25 Oct 2006 22:30:01 -0700, Tiffany wrote:
>
> Hi Tiffany,
> Use a CASE expression:
> SELECT CASE
> WHEN ColumnNameHere NOT LIKE '%[^0-9]%'
> THEN '00' ELSE '' END + ColumnNameHere
> FROM TableNameHere;
> --
> Hugo Kornelis, SQL Server MVP
|||On Thu, 26 Oct 2006 13:41:15 -0700, Arnie Rowland wrote:

>Hugo,
>Instead of combining NOT LIKE and [^0-9], wouldn't that be easier to understand as:
>SELECT CASE
> WHEN ColumnNameHere LIKE '%[0-9]%' THEN '00'
> ELSE ''
> END + ColumnNameHere
>FROM TableNameHere;
>(I've always hated double negatives...)
Hi Arnie,
Easier to understand, but incorrect as well. Try it with '1s3' or 'af7'
as input data.
Double negatives are a bane for language, but a boon for SQL. Not only
for relational division, but also for constructing LIKE clauses. The
very limited regexp powers of LIKE don't allow for a test of "all
characters have to be numeric", but they do allow a test for "no
character may not be numeric".
Of course, if the length of the string is guaranteed to be always three
characters, you could also use LIKE '[0-9][0-9][0-9]'.
Hugo Kornelis, SQL Server MVP
|||I see your point Hugo.
Your illustration of using the double negative will cover all
possibilities -pure or mixed alphanumerics, whereas my method only covers
all alpha or all numeric -but not the mixed possibilities.
For the OP's situation as presented (all alpha or all numeric), either will
work, but your method is definitely more robust.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:beb2k21s9ptltdqsou2dojs4ejbmpdcbt4@.4ax.com...
> On Thu, 26 Oct 2006 13:41:15 -0700, Arnie Rowland wrote:
>
> Hi Arnie,
> Easier to understand, but incorrect as well. Try it with '1s3' or 'af7'
> as input data.
> Double negatives are a bane for language, but a boon for SQL. Not only
> for relational division, but also for constructing LIKE clauses. The
> very limited regexp powers of LIKE don't allow for a test of "all
> characters have to be numeric", but they do allow a test for "no
> character may not be numeric".
> Of course, if the length of the string is guaranteed to be always three
> characters, you could also use LIKE '[0-9][0-9][0-9]'.
> --
> Hugo Kornelis, SQL Server MVP
|||the isnumeric function is probably the most efficient way of accomplishing
this task
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23ZcV%23YZ%23GHA.4704@.TK2MSFTNGP04.phx.gbl.. .
>I see your point Hugo.
> Your illustration of using the double negative will cover all
> possibilities -pure or mixed alphanumerics, whereas my method only covers
> all alpha or all numeric -but not the mixed possibilities.
> For the OP's situation as presented (all alpha or all numeric), either
> will work, but your method is definitely more robust.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:beb2k21s9ptltdqsou2dojs4ejbmpdcbt4@.4ax.com...
>
|||On Fri, 27 Oct 2006 09:44:03 -0400, Michael Abair wrote:

>the isnumeric function is probably the most efficient way of accomplishing
>this task
Hi Michael,
Except that isnumeric returns 1 if the value passed to it can be
converted to at least one numeric datatype - not necessary int.
Try
SELECT ISNUMERIC('$'), ISNUMERIC('1E3'), ISNUMERIC('1.d2')
Hugo Kornelis, SQL Server MVP
sql

Qs. for the experts here...Help!

We are doing a community wide project where we need to extract phone numbers and emails from over 1000s local non-profit and gov. webpages. Any suggestion on the best tool outthere that could help us automate this somewhat?

Well... I'm not a specialist like many other here, and I'm not sure if I got your problem, but I think what you need is a crawler to connect the webpages and go through it's structure.

Once connected to the webpage, you will need a HTML parser (or a specific parser to the format you're facing) to clean the tags, and then, with clean and readable text, extract the information you need.

Parsing isn't an easy task. I've done one almost two years ago and it isn't 100% perfect yet. More tags and formats are always comming (clean LaTeX formats, for example, is a very hard task).

I can't give the crawler/parser to you because it belongs to the university I work, but it works like this:

1) Connect to a given URL

2) Be sure the extension is known (.html, .xhtml, .jsp, .asp, .xml, .php, etc...)

2) Find links (a href, etc...) and put then on a row. If the link is relative, transform it on a absolute link.

3) Parse the URL.

4) Store the information you need and do the appropriated calculations.

5) Goto 1 using the next link on the row.

So... In this metodology, you will need to tell the algorithm an initial webpage we call "seed".