SQL Statement WHERE ignores trailing punctuation

T

Toni

I need to match a string in a database, but ignore any trailing punctuation such as a
period, exclamation point, or question mark.

for example,

SELECT * FROM tbl_stuff WHERE phrase = term

So, if row 5 of the database contains phrase="This is great and exciting!"
and term = "This is great and exciting" (no trailing exclamation point),
it will match row 5.

But if term = "This is great", it will not match.

Can anyone please help?

Thanks!!!
 
B

Bob Barrows

Toni said:
I need to match a string in a database, but ignore any trailing
punctuation such as a period, exclamation point, or question mark.

for example,

SELECT * FROM tbl_stuff WHERE phrase = term

So, if row 5 of the database contains phrase="This is great and
exciting!" and term = "This is great and exciting" (no trailing
exclamation
point), it will match row 5.

But if term = "This is great", it will not match.

Can anyone please help?

Thanks!!!

You will have to use LIKE and append a single-character wildcard to the end
of the search string.

WHERE phrase like 'This is great and exciting_'
 
A

Adrienne Boswell

I need to match a string in a database, but ignore any trailing
punctuation such as a period, exclamation point, or question mark.

for example,

SELECT * FROM tbl_stuff WHERE phrase = term

So, if row 5 of the database contains phrase="This is great and
exciting!" and term = "This is great and exciting" (no trailing
exclamation point), it will match row 5.

But if term = "This is great", it will not match.

Can anyone please help?

Thanks!!!

Two things:
1. It's not a good idea to SELECT * - you should always explicitly state
the columns you need in a query. You can Google for the reasons why.
2. What you need is LIKE:
SELECT fields FROM TABLE WHERE field LIKE 'This is great and exciting%'
The percent sign will catch anything where "This is a great and
exciting" is at the front of the phrase.
 
T

Toni

Bob Barrows said:
You will have to use LIKE and append a single-character wildcard to the end of the
search string.

WHERE phrase like 'This is great and exciting_'

Does the single-character widlcard match zero or one char?
Also, what if the trailing char is not punctuation, as in

House
Houses
Houser

....etc...?
 
T

Toni

Adrienne Boswell said:
:
2. What you need is LIKE:
SELECT fields FROM TABLE WHERE field LIKE 'This is great and exciting%'
The percent sign will catch anything where "This is a great and
exciting" is at the front of the phrase.

But what if the database contains
4 - This is Great
5 - This is Great and Exciting

Searching for "This is great%" will match both fields, right?
 
B

Bob Barrows

Toni said:
Does the single-character widlcard match zero or one char?

Oh! My bad ... one character. There are workarounds that depend on what
database you are using.
Also, what if the trailing char is not punctuation, as in

House
Houses
Houser

any character. All of these will match.
You can do limited pattern-matching, but the database you are using
imposes limits on the type of patterns that can be matched. I won't try
to go into detail until you confirm what database type and version you
are using.
 
T

Toni

Bob Barrows said:
Oh! My bad ... one character. There are workarounds that depend on what
database you are using.


any character. All of these will match.
You can do limited pattern-matching, but the database you are using
imposes limits on the type of patterns that can be matched. I won't try
to go into detail until you confirm what database type and version you
are using.

Bob, my hosting plan has me on MS SQL 2005. However, I do have the option to upgrade to
MS SQL 2008.
 
B

Bob Barrows

Toni said:
Bob, my hosting plan has me on MS SQL 2005. However, I do have the
option to upgrade to MS SQL 2008.

I could have sworn I replied to this but I don't see my reply here. What I
said was:
with SQL Server, you can do something like this:

WHERE phrase=term or phrase like term + '[.,!,?]'
 
B

Bob Barrows

Toni said:
Bob, my hosting plan has me on MS SQL 2005. However, I do have the
option to upgrade to MS SQL 2008.

With SQL2005, you can do something like this:

WHERE phrase = term OR phrase LIKE term + '[.,!,?]'

Look up LIKE in SQL BOL (Books OnLine)
 
T

Toni

With SQL2005, you can do something like this:

WHERE phrase = term OR phrase LIKE term + '[.,!,?]'

Ah! So, if I put the terms in [brackets], it treats it like a RegEx "or"!

That should do it, thanks Bob!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top