Wildcard / Like groupings - SQL Server Forums

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

Transact-SQL (2000)

Wildcard / Like groupings

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2005-08-23 : 06:21:56

Hello,

I have a table of data that I need to group by certain key words in the title and then to return the total amount for each of those groupings.

For example : -

FIELD 1 | FIELD 2

My Apple Pie | 1.99
Apples | 2.99
Oranges | 0.99
Orange Vest | 2.99
Apple Turnovers | 2.99

So the result I would like back is : -

Apples 7.97
Oranges 3.98

Any ideas without having to do a load of different calls to the database and creating temp tables. Which I feel is the only way to do this!!

Your help would be appreciated

SMc
Starting Member

7 Posts

Posted - 2005-08-23 : 06:50:35

Assuming you know the keywords you can do something like:

CREATE TABLE test (item char(15), cost money)

INSERT INTO test VALUES ('My Apple Pie', 1.99)
INSERT INTO test VALUES ('Apples', 2.99)
INSERT INTO test VALUES ('Oranges', 0.99)
INSERT INTO test VALUES ('Orange Vest', 2.99)
INSERT INTO test VALUES ('Apple Turnovers', 2.99)

SELECT s.term, SUM(t.cost)
FROM test t
INNER JOIN (SELECT 'Apple' AS term UNION SELECT 'Orange') s ON t.item LIKE '%' + s.term + '%'
GROUP BY term

DROP TABLE test

This gives the results you want, you could always use another table to hold the search terms and join to that instead.

jparker
Posting Yak Master

118 Posts

Posted - 2005-08-23 : 07:04:46

Excellent so what if I wanted to do the following : -

CREATE TABLE test (item char(15), cost money)

INSERT INTO test VALUES ('My Apple Pie', 1.99)
INSERT INTO test VALUES ('Apples', 2.99)
INSERT INTO test VALUES ('Banana', 0.99)
INSERT INTO test VALUES ('Orange Vest', 2.99)
INSERT INTO test VALUES ('Apple Turnovers', 2.99)

CREATE TABLE test1 (item char(15))
INSERT INTO test1 VALUES ('Apple')
INSERT INTO test1 VALUES ('Banana')
INSERT INTO test1 VALUES ('Oranges')

SELECT s.term, SUM(t.cost)
FROM test t
INNER JOIN (SELECT item AS term from test1) s ON t.item LIKE '%' + s.term + '%'
GROUP BY term

DROP TABLE test
DROP TABLE test1

I get the results

Banana .9900

Whereas I wanted all three. Can you help please

SMc
Starting Member

7 Posts

Posted - 2005-08-23 : 07:17:25

You need to change

LIKE '%' + s.term + '%'

to

LIKE '%' + RTRIM(s.term) + '%'

in your example because you have used a char field in test1 - changing the table to use varchar instead would also solve this problem.

Note that in your example you will still only get results for Apple and Banana because Oranges doesn't match any of the strings you are searching, it would have to be Orange.

HTH,
Steve

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-23 : 07:20:39

Otherwise use varchar data type

Madhivanan

Failing to plan is Planning to fail

jparker
Posting Yak Master

118 Posts

Posted - 2005-08-23 : 07:20:42

Excellent thank you for all your help SMc

final code : -

CREATE TABLE test (item varchar(25), cost money)

INSERT INTO test VALUES ('My Apple Pie', 1.99)
INSERT INTO test VALUES ('Apples', 2.99)
INSERT INTO test VALUES ('Banana', 0.59)
INSERT INTO test VALUES ('Oranges Vest', 2.99)
INSERT INTO test VALUES ('Apple Turnovers', 2.99)

CREATE TABLE test1 (item varchar(15))
INSERT INTO test1 VALUES ('Apple')
INSERT INTO test1 VALUES ('Banana')
INSERT INTO test1 VALUES ('Orange')

SELECT s.term, SUM(t.cost)
FROM test t
JOIN (SELECT item AS term from test1) s ON t.item LIKE '%' + s.term + '%'
GROUP BY s.term

DROP TABLE test
DROP TABLE test1

RESULT

Apple 7.9700
Banana .5900
Orange 2.9900

SMc
Starting Member

7 Posts

Posted - 2005-08-23 : 07:32:28

You're welcome :)

jeffreyfrog
Starting Member

1 Post

Posted - 2009-06-04 : 17:05:35

I think all of them answer your question,, butif you have any question or problem may this could help you..[url]team building[/url]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 17:14:47

What is the reason for you to do that? (kaput link)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-05 : 02:14:00

quote:
Originally posted by jeffreyfrog

I think all of them answer your question,, butif you have any question or problem may this could help you..[url]team building[/url]

How can it be helpful?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources