Re: SQL SELECT with Similar Terms

Hi,

I prefere the scalar function TRIM:

select trim(Both ':' from RTrim(MyField)), count(*)
from mytable
group by Trim(Both ':' from RTrim(MyField))

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Vernon Hamberg
Gesendet: Friday, August 17, 2007 15:41
An: Midrange Systems Technical Discussion
Betreff: Re: SQL SELECT with Similar Terms


Michael

Based on another recent post, here's a way to get what you want--

select strip(translate(char20,' ', ':')), count(*)
from mryan
group by strip(translate(char20,' ', ':'))

If you had more punctuations, just add them to the 3rd parameter of
translate().

HTH
Vern

At 08:18 AM 8/17/2007, you wrote:

Anyone know how to do this? I have data that looks like this:

Rattan
Traditional
:Rattan:
Contemporary

I'd like to produce a list like

Rattan 2
Traditional 1
Contemporary 1

The 'extra' search result with the colons around it should be the same
as the search result without the colons.

Thanks...
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.





--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.