Tech Off Thread

23 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

hopefully simple t-sql question

Back to Forum: Tech Off
  • User profile image
    ferguslogic

    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.




  • User profile image
    Maddus Mattus

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

  • User profile image
    spivonious

    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

  • User profile image
    ferguslogic

    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.

  • User profile image
    stevo_

    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

  • User profile image
    Matthew van Eerde

    +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.

  • User profile image
    Maddus Mattus

    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?

  • User profile image
    spivonious

    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:

  • User profile image
    ferguslogic

    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.

  • User profile image
    Matthew van Eerde

    ferguslogic said:
    spivonious said:
    *snip*

    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.

    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.

  • User profile image
    ferguslogic

    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.

  • User profile image
    harumscarum

    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.

    Sad 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.

  • User profile image
    Maddus Mattus

    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.

    Sad 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?

  • User profile image
    TommyCarlier

    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.

  • User profile image
    Maddus Mattus

    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 Wink

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

  • User profile image
    TommyCarlier

    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 Wink

    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.

  • User profile image
    wisemx

    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. Smiley
    http://blogcastrepository.com/blogs/wisemx/archive/2008/06/12/sql-server-2008-express-rc0.aspx

  • User profile image
    Maddus Mattus

    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 Big Smile

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

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.