sql query with punctuation

[redgar]

I have a database that I am querying that contains text. In searching I encounter instances of words followed by punctuation like commas, colons periods and so forth. I don't want to use two %'s around my queries because it will include all sorts of other words (child search includes children).

I want to include my search words without having to write a long line of ORs to include the word and all forms of puntuation. Is there any kind of wildcarding I can do for this. I tried doing some brackets and underscores etc but my queries are really turning out pretty badly. I'd like to be able to totally control what is returned.

Even on my broad search where I am not looking for exact matches if I use something like WHERE Text LIKE '% ' + ? + '_ %' I end up getting words like wine or wind when looking up win. Can anyone point me in the right direction for both a loose match and exact match, especially where punctuation is concerned?

 

[cjard]

I probably could if I could work out what the heck you were talking about.

Why dont you include FIVE example lines of text that represent all cases you are concerned about, ONE search word that you are looking for and SOME result lines and why they matched, and an explanation of why the others didnt match


Rememeber that youre talking to computer programmers; they think like the computers they program. This post is far too wooly and doesnt make a strong enough point to enable me to work out how to answer it

 

[redgar]

The first paragraph describes in no uncertain terms exactly what I am trying to do.

I'm querying a text field in my database. When using ... WHERE TextField = "whatever", I of course don't return anything unless it matches an entire line of text in a given column (not happening of course with fields containing much text). I am therefore using statements with LIKE to query the database such as WHERE Text LIKE '% ' + ? + '_ %' (as already mentioned).

So if I want to find all fields where the text contains the word win, I am getting the matches for my word followed by punctuation as desired (using the _ wildcard), but also wind, wine, and so forth. When I use WHERE textField LIKE '% ' + ? + '%', I get even more problems...

Now heres the main problem: when I try to specify my parameter in between two spaces (WHERE TextField LIKE '% ' + ? + ' %') I then lose any words that are followed by punctuation (like "win,", "win:", "win." and so forth).

I believe I can concatenate my parameter with an type thing for plural words, but how would I find these words with punctuation after the word? I can specify a range of letters or number values using [a-n] or [1-5] but I see no wildcards for punctuation.

Thats my question. I know not how else to elaborate further.

 

[InertiaM]

I know not how else to elaborate further.

How about answering this :-

Why dont you include FIVE example lines of text that represent all cases you are concerned about, ONE search word that you are looking for and SOME result lines and why they matched, and an explanation of why the others didnt match

I'm not trying to be negative, but cjard has an extremely valid point. If we can see the data you have, and what you are trying to achieve, it makes it a LOT easier. Is your search case-sensitive? You havent said, so we have to guess.

For example, if you are searching for "win", which of the following are acceptable matches?

The window was open
Open the window
Open the window!!
Open the window, Win!
Who opened the window?
Win opened the window
Win opened the window.
The window was opened by Win
There was light coming in the window; Win opened it.

Anyway, back to the point of the post. I expect there may be a simple answer, but I'd run a subquery to strip out all the punctuation first, and then run your search query based on the results of the subquery.

 

[cjard]

search using a regular expression:

select * from stories where dbo.RegexMatch(story_text, '\b'+@word+'\b' ) = 1


\b meaning "word boundary"

http://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/
http://www.regular-expressions.info/wordboundaries.html