Linguistic query 2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Linguistic query 2

Author

Topic

ACCESS-SQL_MARIO
Starting Member

18 Posts

Posted - 2004-11-12 : 13:04:10

Salve!

I have a column of sentences. And I have a form where I can type in a word and press enter to find a sentence with it. I inherited the following query but I want to modify it.

SELECT SentencesTable.SentencesColumn
FROM SentencesTable
WHERE SentencesTable.SentencesColumn
LIKE '*" + typedinword + "*';

BUT in the above query
when I type in eg DECENT
it finds also sentences with DECENTLY, INDECENT
AND I want sentences only with DECENT
maybe like that ?:

LIKE * + " " + typedinword " " + *

but it doesn't work

Disclaimer: I am only BA in Teaching English

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-12 : 13:33:08

it should be

Like * & " " & typedinword & " " & *

Concatination in Access uses the &

ACCESS-SQL_MARIO
Starting Member

18 Posts

Posted - 2004-11-12 : 15:08:53

thanks, I'll correct it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-12 : 15:37:14

actually, the best solution might be something like this:

WHERE ' ' & Column LIKE "*[!a-z]" & typedinword & "[!a-z]*"

it will allow for your word to be found surrounded by any characters other than A-Z. so it will match words ending in commas or periods or puncuation. Change the [!a-z] part as needed to exclude other possibilities before or after the word.

also, note the concatenation of a space before the column to ensure the first word is found.

- Jeff

Auric
Yak Posting Veteran

70 Posts

Posted - 2004-11-12 : 16:02:29

hrm.. interesting Jeff

Never would have thought of that one

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 16:09:17

well if your going to concatenate at the beginning you probably should do that to the end as well:

WHERE ' ' & Column & ' ' LIKE "*[!a-z]" & typedinword & "[!a-z]*"

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-12 : 17:16:47

yeah, i thought about that, but figured it would usually end in a period if these are sentences, so it would not be necessary .... but i guess you never know!

- Jeff

ACCESS-SQL_MARIO
Starting Member

18 Posts

Posted - 2004-11-13 : 15:00:21

Thanks JEFF

I must check all this at my office on Monday but Jeff, Thanks AGAIN.

So now:
a) the first words are also found
DECENT people don't do this stuff.
b) words before punctuation ( , or . ) are also found
The lady, whom you regard as decent, is vulgar.
Mario

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-13 : 16:42:40

and last words (w/o punctuation):

That meal was decent

Corey

ACCESS-SQL_MARIO
Starting Member

18 Posts

Posted - 2004-11-15 : 11:06:30

one more thing:
WHERE ' ' & Column LIKE "*[!a-z]" & typedinword & "[!a-z]*"
works perfectly well on ACCESS 2000 (my home)
but won't on ACCESS 97(my office)...?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-15 : 11:20:52

try:

WHERE " " & Column LIKE "*[!a-z]" & typedinword & "[!a-z]*"

(note the double-quotes -- the first example inconsistently used single and double quotes to delimit text). maybe Access 97 is more sensitive to this. but I am sure that they both implement LIKE in the same way (check Access 97 HELP if you're not sure -- I don't have it handy right now).

- Jeff

ACCESS-SQL_MARIO
Starting Member

18 Posts

Posted - 2004-11-15 : 11:25:13

aye aye sir!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources