Post reply

problem in sql query

  • November 20, 2004 at 6:52 pm

    #361621

    i have a problem in a sql query.

    in my sql server database i have a table called DATA_HOTEL .This table has a field called

    hotelName. it has a data "Grand Hyatt Hotel,New York".

    if anybody give me hotelName in different order of  words for this data i will acept that it

    is in the database.

    i.e if anybody give me

    "Hyatt Hotel  Grand,New York"  // ok, it is in the database, ordering no matter

    "Hyatt Grand Hotel,New York  // ok,it is in the database,ordering no matter

    "New York,Grand Hyatt Hotel" // ok,it is in the database,ordering no matter

     

    "Hyatt Hotel  Grand,New York and spa" // ok extra thing also allowed

    look ordering does not matter to me,although they may come in different order.

    what would be my sql query which does not care for ordering of the words like above?

    can u give an example ?

       thanks all         

  • November 21, 2004 at 9:00 pm

    #530983

    hi it seems difficult. is  not it possible by using "Like" keyword ?

  • November 21, 2004 at 11:35 pm

    #530985

    It is possible, however bad database design leads to overly complex queries. and inaccurate results from queries

    It is a much better solution to have a properly normalised database structure and clean data.

    I worked with data the looked like that several years ago and there's another complication that you may not have considered - Spelling mistakes. It ended up taking me longer to clean the data than to pull the stats they wanted.

    I don't know what else is in your table, but I would echo Joe's recomendation that you split the HotelName into two columns. - Name and location

    I would even go so far as to suggest you create a second table called Locations with LocationID and Location fields, store the locationID in the Hotels table and create a foreign key. That prevents you having dup rows because someone types 'New York', someone else misstyped 'New Yokr' and someone else entered 'NY'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 22, 2004 at 3:31 am

    #530998

    In answer to your question....NO there is no simple query that would return what you want. Why not? Because of the poor data.

    You can't ask for LIKE %HYATT GRAND% because Hyatt and Grand are not always next to each other in that order. You can't ask for LIKE %HYATT% because you might have other Hyatt Hotels besides the Grand.

    You would have to do a search similar to:

    WHERE Data_Hotel LIKE '%Hyatt%'

       AND Data_Hotel LIKE '%Grand%'

    But that search would take FOREVER. If you have indexes, they would be ignored so each row would have to be searched twice. Not efficient at all. You are better off doing what the others have suggested and redesigning the database and fixing the data.

    -SQLBill

  • November 22, 2004 at 6:28 am

    #531016

    I agree with all of the others. 

    Bad design is bad design.

    Sure, you can build it any way you want to.  But, there is a price to pay for doing it wrong.  The right way is to fully normalize the design.

    As a long time fan of Celko's, you will not go wrong taking his advice.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply