Search Multiple Columns with REGEXP

[Rizyak]

*************** *****
alt.php.sql,com p
databases.ms-sqlserver
microsoft.publi c.sqlserver.pro gramming
*************** *************** *****

Why doesn't this work:

SELECT *
FROM 'Events'
WHERE dayofweek
REGEXP 'monday' OR description REGEXP 'monday'

When this does work:
SELECT *
FROM `Events`
WHERE dayofweek
REGEXP 'monday'

[Jacco Schalkwijk]

Re: Search Multiple Columns with REGEXP

REGEXP is not a part of Transact-SQL.

--
Jacco Schalkwijk
SQL Server MVP

"Rizyak" <ryanREMOVEME@l atitude47.comAN DMETOO> wrote in message
news:cc1tlf$nec $1@gnus01.u.was hington.edu...[color=blue]
> *************** *****
> alt.php.sql,com p
> databases.ms-sqlserver
> microsoft.publi c.sqlserver.pro gramming
> *************** *************** *****
>
> Why doesn't this work:
>
> SELECT *
> FROM 'Events'
> WHERE dayofweek
> REGEXP 'monday' OR description REGEXP 'monday'
>
> When this does work:
> SELECT *
> FROM `Events`
> WHERE dayofweek
> REGEXP 'monday'
>
>
>[/color]

[Rizyak]

Re: Search Multiple Columns with REGEXP

hmmm, is there a way to search multiple fields for keywords?
I want to search an entire table for a keyword if possible.


"Rizyak" <ryanREMOVEME@l atitude47.comAN DMETOO> wrote in message
news:cc1tlf$nec $1@gnus01.u.was hington.edu...[color=blue]
> *************** *****
> alt.php.sql,com p
> databases.ms-sqlserver
> microsoft.publi c.sqlserver.pro gramming
> *************** *************** *****
>
> Why doesn't this work:
>
> SELECT *
> FROM 'Events'
> WHERE dayofweek
> REGEXP 'monday' OR description REGEXP 'monday'
>
> When this does work:
> SELECT *
> FROM `Events`
> WHERE dayofweek
> REGEXP 'monday'
>
>
>[/color]

[Mischa Sandberg]

Re: Search Multiple Columns with REGEXP

In SQLese:

select * from Events where dayofweek like '%monday%' or description like
'%monday%'

This searches ALL ROWS of a table (if that's what you mean by,'search an
entire table').
There's no builtin mechanism for searching all columns (no, you can't write:

select * from Events where * like '%monday%'

But if you're willing to type out a lot of 'or' predicates, you can extend
the first example to as many columns as you think appropriate.

The fact that you used REGEXP instead of LIKE suggests you're looking for
PHP/Perl-style operators in SQL. Sorry, no such luck.

"Rizyak" <ryanREMOVEME@l atitude47.comAN DMETOO> wrote in message
news:cc21nn$pf3 $1@gnus01.u.was hington.edu...[color=blue]
> hmmm, is there a way to search multiple fields for keywords?
> I want to search an entire table for a keyword if possible.
>
>
> "Rizyak" <ryanREMOVEME@l atitude47.comAN DMETOO> wrote in message
> news:cc1tlf$nec $1@gnus01.u.was hington.edu...[color=green]
> > *************** *****
> > alt.php.sql,com p
> > databases.ms-sqlserver
> > microsoft.publi c.sqlserver.pro gramming
> > *************** *************** *****
> >
> > Why doesn't this work:
> >
> > SELECT *
> > FROM 'Events'
> > WHERE dayofweek
> > REGEXP 'monday' OR description REGEXP 'monday'
> >
> > When this does work:
> > SELECT *
> > FROM `Events`
> > WHERE dayofweek
> > REGEXP 'monday'
> >
> >
> >[/color]
>
>[/color]

[John Bell]

Re: Search Multiple Columns with REGEXP

Hi

Maybe something like the following would help:

http://www.users.drew.edu/skass/sql/SearchAllTables.sql.txt

John
"Rizyak" <ryanREMOVEME@l atitude47.comAN DMETOO> wrote in message
news:cc21nn$pf3 $1@gnus01.u.was hington.edu...[color=blue]
> hmmm, is there a way to search multiple fields for keywords?
> I want to search an entire table for a keyword if possible.
>
>
> "Rizyak" <ryanREMOVEME@l atitude47.comAN DMETOO> wrote in message
> news:cc1tlf$nec $1@gnus01.u.was hington.edu...[color=green]
> > *************** *****
> > alt.php.sql,com p
> > databases.ms-sqlserver
> > microsoft.publi c.sqlserver.pro gramming
> > *************** *************** *****
> >
> > Why doesn't this work:
> >
> > SELECT *
> > FROM 'Events'
> > WHERE dayofweek
> > REGEXP 'monday' OR description REGEXP 'monday'
> >
> > When this does work:
> > SELECT *
> > FROM `Events`
> > WHERE dayofweek
> > REGEXP 'monday'
> >
> >
> >[/color]
>
>[/color]

[Erland Sommarskog]

Re: Search Multiple Columns with REGEXP

Rizyak (ryanREMOVEME@l atitude47.comAN DMETOO) writes:[color=blue]
> hmmm, is there a way to search multiple fields for keywords?
> I want to search an entire table for a keyword if possible.[/color]

If you are actually using SQL Server, you may investigate whether full-text
search can meet your needs. I have never used the feature myself, though.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[John Kane]

Re: Search Multiple Columns with REGEXP

Hi Erland,
Someone else (I'm not sure it was Rizyak) posted a simalar question on
REGEXP recently in the fulltext newsgroup and I replied to it, with
recommendations to use LIKE and to review the BOL titles "Pattern Matching
in Search Conditions" and "PATINDEX" as well as "Comparing CHARINDEX and
PATINDEX" as SQL Full-text Search (FTS) is not designed for string pattern
matching such as you would get with REGEXP. FTS is more of a word-based
search method vs. T-SQL LIKE's pattern-string method. So, hopefully between
us he or they got their answer... Bellow is an Patindex T-SQL example:

use Northwind
select Description from Northwind.dbo.C ategories
where patindex('%[b,B]read%',descript ion) > 0
and patindex('_[^e]%',description) = 1
/* -- returns:
Description
---------------------------------------
Breads, crackers, pasta, and cereal
(1 row(s) affected)
*/

Note, you can also post FTS related questions to the newsgroup:
microsoft.publi c.sqlserver.ful ltext
Regards,
John




"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns951B32D D28C4Yazorman@1 27.0.0.1...[color=blue]
> Rizyak (ryanREMOVEME@l atitude47.comAN DMETOO) writes:[color=green]
> > hmmm, is there a way to search multiple fields for keywords?
> > I want to search an entire table for a keyword if possible.[/color]
>
> If you are actually using SQL Server, you may investigate whether[/color]
full-text[color=blue]
> search can meet your needs. I have never used the feature myself, though.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]