473,326 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Search Multiple Columns with REGEXP

********************
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
6 7176
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Anand Pillai | last post by:
To search a word in a group of words, say a paragraph or a web page, would a string search or a regexp search be faster? The string search would of course be, if str.find(substr) != -1:...
2
by: gyromagnetic | last post by:
Hi, I have written a function that searches a text string for various words. The text is searched using a boolean 'and' or a boolean 'or' of the input list of search terms. Since I need to use...
1
by: Tim Arnold | last post by:
Hi, I've got a list of 1000 common misspellings, and I'd like to check a set of text files for those misspellings. I'm trying to figure out the fastest way to do it; here's what I'm doing now...
12
by: Rizyak | last post by:
This is x-posted in: alt.php.sql comp.databases.ms-sqlserver microsoft.public.sqlserver.programming I have events that occur during the day. I want to be able to search those by a form with...
1
by: B | last post by:
Hello All, This is my first time using this list, but hopefully I got the right one for the question I need to ask :). I have a table which has about 4 million records. When I do a search...
5
by: vonclausowitz | last post by:
Repost from an VB group. Hi All, I'm looking for a way to search for multiple words in a database. There is however one but. The words have to be within a certain range of each other. For...
1
by: terence.parker | last post by:
I am trying to do a search through some data, more specifically HTML, to extract data from it. So for example I may have: <b>Title:</b<em>This is a title</em> <b>Name:</b<em>Fred</em> I wish...
47
by: Henning_Thornblad | last post by:
What can be the cause of the large difference between re.search and grep? This script takes about 5 min to run on my computer: #!/usr/bin/env python import re row="" for a in range(156000):...
8
by: Sham | last post by:
I am trying to perform the following query on a table that has been indexed using Full Text Search. The table contains multiple columns than have been indexed. (Below, all xml columns are...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.