Post reply

Simple Select statement help

  • December 6, 2006 at 9:35 am

    #175558

    I can't figure out how to make this simple select statement work:

    select * from My_Table where show_name =  'My hunter's journal'

    Obvioulsy I don't know how to deal with the apostrophe in the show name.. 

    and I also can't figure how to to use the %LIKE clause..  maybe that would help also???

  • December 6, 2006 at 9:47 am

    #676307

    select * from My_Table where show_name =  'My hunter''s journal'

     

     

    cheers

  • December 6, 2006 at 9:50 am

    #676308

    so you replace the apostrophe in  Hunter's with double quotes?

    I tried it and it returned zero rows ???

  • December 6, 2006 at 9:54 am

    #676311

    Should work... Try this:

    SELECT show_name FROM My_Table WHERE show_name LIKE 'My hunter%'

    This will verify whether you have the correct data in your table.

    John

  • December 6, 2006 at 10:04 am

    #676315

    >>so you replace the apostrophe in  Hunter's with double quotes?

    No, you replace the embedded single quote with two single quotes, not 1 double quote.

     

  • December 6, 2006 at 11:53 am

    #676364

    Thank you so much.. both worked!!!!!!!!!!!!!!!

    Kind of on the same note...  Now that I have been able to list all of my records using

    select * from My_Table where show_name =  'My hunter''s journal'

    How can I use T-SQL to change the actual show name (s)  from

    My Hunter's journal   to         My Hunter's notebook

    ?????

  • December 6, 2006 at 12:56 pm

    #676387

    You can use replace function...

    select replace(show_name, 'journal','Notebook') as show_name from My_Table where show_name =  'My hunter''s journal'

     

    select replace('abcedfg', 'ab','xx')

    MohammedU
    Microsoft SQL Server MVP

  • December 6, 2006 at 11:58 pm

    #676514

    It's time you used BOL a bit more - there is absolutely heaps of info there. Your last two queries are well handled by Books OnLine.

    It is possible that BOL is not installed if a custom installation was done that excludes BOL - but I'd never reccommend doing that.

    You'll find info on "wildcards", "functions" (string functions etc) and a lot more.

    I usually do a 'search' and click the top of the column 'title' to alphabetically order the results. BOL offers searching in a few ways - experiment.

    Still, don't be afraid to post questions - the asking may help someone else too and prompt (hopefully?) useful responses.

    Cheers,

    DB


    The systems fine with no users loggged in. Can we keep it that way ?br>

  • December 7, 2006 at 5:11 am

    #676549

    Thanks All.   I'm certain I would have found the sytax for the REPLACE in BOL - admittedly I didn't look - but I did try and figure out the wildcard stuff in the select statement from BOL and just was not able to get the syntax right!!!!!!

    Thanks again everyone.

  • December 7, 2006 at 6:01 am

    #676555

    Can  post when i have an issue with notepad?

     

    (sorry - worked late, feeling rude today)

  • December 7, 2006 at 6:47 am

    #676573

    You can use SET QUOTED_IDENTIFIER ON.  This will allow you to use double quotes as string delimiters so that you could write :

    select * from My_Table where show_name =  "My hunter's journal".

    Make sure to return it to its original state (presumably OFF in your case) immediately after the query !

  • December 7, 2006 at 7:18 am

    #676590

    sorry..  I think I missed something on this one.. 

  • December 7, 2006 at 7:31 am

    #676597

    I tried the replace you mention..

    here is my select statement.. returns 522 rows

    select * from schedule_storer where Storer_Show_Name = 'AMERICA''S HORSE'

    Here is my Select Replace.. says it affects 522 rows when I run it..

    select replace  (storer_show_name, 'HORSE','HORSES') as storer_show_name from schedule_storer where storer_show_name =  'AMERICA''S HORSE'

    Now when I rerun my Select Statement, it returns the 522 rows... but the storer_show_name has not changed.. 

     

  • December 7, 2006 at 7:46 am

    #676603

    If you mean to permanently change the values in the db table then use an UPDATE statement.

    UPDATE My_Table

    SET show_name =  'My hunter''s notebook'

    WHERE show_name =  'My hunter''s journal'

  • December 7, 2006 at 8:16 am

    #676627

    awesome!  THanks

Viewing 15 posts - 1 through 15 (of 16 total)

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