By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,992 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,992 IT Pros & Developers. It's quick & easy.

Search Multiple Columns with REGEXP

P: n/a
********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************

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'

Jul 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
REGEXP is not a part of Transact-SQL.

--
Jacco Schalkwijk
SQL Server MVP

"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cc**********@gnus01.u.washington.edu...
********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************

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'

Jul 20 '05 #2

P: n/a
hmmm, is there a way to search multiple fields for keywords?
I want to search an entire table for a keyword if possible.
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cc**********@gnus01.u.washington.edu...
********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************

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'

Jul 20 '05 #3

P: n/a
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" <ry**********@latitude47.comANDMETOO> wrote in message
news:cc**********@gnus01.u.washington.edu...
hmmm, is there a way to search multiple fields for keywords?
I want to search an entire table for a keyword if possible.
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cc**********@gnus01.u.washington.edu...
********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************

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'


Jul 20 '05 #4

P: n/a
Hi

Maybe something like the following would help:
http://www.users.drew.edu/skass/sql/...Tables.sql.txt

John
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cc**********@gnus01.u.washington.edu...
hmmm, is there a way to search multiple fields for keywords?
I want to search an entire table for a keyword if possible.
"Rizyak" <ry**********@latitude47.comANDMETOO> wrote in message
news:cc**********@gnus01.u.washington.edu...
********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************

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'


Jul 20 '05 #5

P: n/a
Rizyak (ry**********@latitude47.comANDMETOO) writes:
hmmm, is there a way to search multiple fields for keywords?
I want to search an entire table for a keyword if possible.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

P: n/a
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.Categories
where patindex('%[b,B]read%',description) > 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.public.sqlserver.fulltext
Regards,
John


"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Rizyak (ry**********@latitude47.comANDMETOO) writes:
hmmm, is there a way to search multiple fields for keywords?
I want to search an entire table for a keyword if possible.
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.