hopefully simple t-sql question

Okay guys i must have done this a million times but i am feeling brain dead today.

I have 1 textbox in the user interface that allows the user to enter search criteria and search a couple of columns in our database in 1 table.

The code that I was using looked like this


(assuming @pisearchtext = "rainy day")

select * from blah blah blah

where

tick.descriptionStripped like '%' + @piSearchText + '%'

or tick.title like '%' + @piSearchText + '%'

or tick.ticketnumber like '%' + @piSearchText + '%'

or tick.keywords like '%' + @piSearchText + '%'

What if i want to search for each word individually however?

I want to locate either the word 'rainy' or 'day'   and not 'rainy day' together.



If i knew there were 2 words i could for example write this where clause

where

tick.descriptionStripped like '%rainy%'  or tick.descriptionstripped like  '%day%'

I do not however know if there will be 1, 2,3, 4 or 18 words so I cannot use logic like this.






I am trying to do this without full text indexing.

I suggest you do take a look at SQL full text search, because you are basically rewriting it.

We run Oracle over here, so I can't help you if any SQL server functions exist for this, but why not just build the where clause dynamically?  Split the textbox.text on a space and loop through each one adding another "OR field LIKE blahblah" each time.

Dim searchItems() as String = textbox.text.Split(" ")

For Dim i as Integer = LBound(searchItems) to UBound(searchItems)
    whereClause &= " OR tick.descriptionStripped LIKE '%" & searchItems(i) & "%' "
Next

spivonious said:

We run Oracle over here, so I can't help you if any SQL server functions exist for this, but why not just build the where clause dynamically?  Split the textbox.text on a space and loop through each one adding another "OR field LIKE blahblah" each time.

Dim searchItems() as String = textbox.text.Split(" ")

For Dim i as Integer = LBound(searchItems) to UBound(searchItems)
    whereClause &= " OR tick.descriptionStripped LIKE '%" & searchItems(i) & "%' "
Next

I would love to do that but I don't think you can build a dynamic where clause in SQL Server and append it to a static sql statement.

You have to generate the entire statement dynamically don't you guys?

I even have the split function already written but I don't think I have a way to do this.

ferguslogic said:

spivonious said:

*snip*

I would love to do that but I don't think you can build a dynamic where clause in SQL Server and append it to a static sql statement.

You have to generate the entire statement dynamically don't you guys?

I even have the split function already written but I don't think I have a way to do this.

I'd look into indexing content (like full text search would), this way you can do searches based upon relation vs comparison of literal values (which is slow)..

If your data is always modern english, you can write a basic word breaker and word stemmer.. you may even be able to find free examples of these.. or maybe even complete systems like lucene

Matthew van Eerde said:

+1 on using full-text search; it's faster than LIKE '%' + foo + '%', too.

Another approach I've used is to build the SQL dynamically within a stored procedure, and then use sp_executesql to run the built SQL.

My collegue always tells me:

'Don't fight the system'

What is the reason nu full text index?

ferguslogic said:

spivonious said:

*snip*

I would love to do that but I don't think you can build a dynamic where clause in SQL Server and append it to a static sql statement.

You have to generate the entire statement dynamically don't you guys?

I even have the split function already written but I don't think I have a way to do this.

Oh, I thought this was for an application on top of the database, not in the database itself. :doh:

spivonious said:

ferguslogic said:

*snip*

Oh, I thought this was for an application on top of the database, not in the database itself. :doh:

No this is a  sql serve stored procedure.

No full text indexing because this is a hosted database at a  webhost using sql server express with no full text indexing available.

Matthew van Eerde said:

ferguslogic said:

*snip*

Yeah this question screams "dynamically built SQL" to me.

My suggestion: sanitize the input in the CGI to be word-ish tokens seperated by, oh, say, commas: @input = 'foo,bar,baz'

Then write T-SQL to loop over the input and build up the WHERE clause:
WHERE field like '%foo%' or field like '%bar%' or field like '%baz%'

Even without full-text searching, this should be doable if you have some good hardware, not very many tickets, and relatively infrequent queries.

thanks for all the assistance everyone.

Maddus Mattus said:

I suggest you do take a look at SQL full text search, because you are basically rewriting it.

hmm this full text search is relevant to my interests. I'll have to snag a 2005 book when I'm back in the office.

ok it seems that the table has to have unique keys....unfortunately for me the major tables in the system I work with have composite keys.

harumscarum said:

Maddus Mattus said:

*snip*

hmm this full text search is relevant to my interests. I'll have to snag a 2005 book when I'm back in the office.

ok it seems that the table has to have unique keys....unfortunately for me the major tables in the system I work with have composite keys.

Just make an ID value that runs along.

And seriously; a hoster that runs on SQL Express? Is that even legal? Or are you talking about a small server in your network?

Maddus Mattus said:

harumscarum said:

*snip*

Just make an ID value that runs along.

And seriously; a hoster that runs on SQL Express? Is that even legal? Or are you talking about a small server in your network?

Why would it not be legal? If you read this, you can see that SQL Server Express is great for dedicated hosting (not recommended for shared hosting). Our company uses SQL Server Express a lot and we love it. Our internal tools run on it (time-tracking tool, ...), some of our web applications use SQL Server Express, our new flagship products (Transceiver Server, Transceiver Automator) run perfectly on SQL Server Express. We're a bit disappointed with SQL Server Compact Edition though: Microsoft markets it as a robust embedded database engine that is ideal for client applications, but we've noticed it does not work that great. It starts getting slow (really slow) if tables are getting too big, and it has very limited support for multithreaded access.

TommyCarlier said:

Maddus Mattus said:

*snip*

Why would it not be legal? If you read this, you can see that SQL Server Express is great for dedicated hosting (not recommended for shared hosting). Our company uses SQL Server Express a lot and we love it. Our internal tools run on it (time-tracking tool, ...), some of our web applications use SQL Server Express, our new flagship products (Transceiver Server, Transceiver Automator) run perfectly on SQL Server Express. We're a bit disappointed with SQL Server Compact Edition though: Microsoft markets it as a robust embedded database engine that is ideal for client applications, but we've noticed it does not work that great. It starts getting slow (really slow) if tables are getting too big, and it has very limited support for multithreaded access.

I love SQL Server Compact Edition! I can read my Windows Media Library with it, it rocks!

I have noticed that it slows down a bit with large sizes, but I just compressed the data I put in it. Takes only processor time of the client, I dont care about that

Maybe I just don't like SQL Server Express, I wouldnt subscribe to a hosting service that runs on that.

Maddus Mattus said:

TommyCarlier said:

*snip*

I love SQL Server Compact Edition! I can read my Windows Media Library with it, it rocks!

I have noticed that it slows down a bit with large sizes, but I just compressed the data I put in it. Takes only processor time of the client, I dont care about that

Maybe I just don't like SQL Server Express, I wouldnt subscribe to a hosting service that runs on that.

I love SQL Server Express! What's not to like? It's exactly like SQL Server (same engine), only ... Express. You can run some hefty applications on it.

TommyCarlier said:

Maddus Mattus said:

*snip*

I love SQL Server Express! What's not to like? It's exactly like SQL Server (same engine), only ... Express. You can run some hefty applications on it.

I wasn't a fan of SQL Express until the 2008 version.
 (Primarily because you couldn't pump like big brother.)

Recently blogged about it and the SQL Server Express team thanked me. Cool.
SQL Server 2008 Express is awesome, hands down I believe it will blow MySQL out of the water.
http://blogcastrepository.com/blogs/wisemx/archive/2008/06/12/sql-server-2008-express-rc0.aspx

TommyCarlier said:

Maddus Mattus said:

*snip*

I love SQL Server Express! What's not to like? It's exactly like SQL Server (same engine), only ... Express. You can run some hefty applications on it.

Database size limit?
Database number limit?
Connection limit?
No fulltext search?
That it installs under /SQLEXPRESS/?
That it's a * to remove from your system?
No client tools?

But hey, it's free

I'll talk to you again when a database reaches it's size limit and access gets blocked.