Simple Select statement help

Question

Post reply

Simple Select statement help

Jpotucek

SSCoach

Points: 18583
More actions
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???

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

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

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

cheers

Jpotucek SSCoach Points: 18583 More actions · Answer 2

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

I tried it and it returned zero rows ???

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

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

Jpotucek SSCoach Points: 18583 More actions · Answer 5

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

?????

MohammedU SSCoach Points: 19644 More actions · Answer 6

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

dbuchan SSCrazy Points: 2328 More actions · Answer 7

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>

Jpotucek SSCoach Points: 18583 More actions · Answer 8

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.

David Wasy SSC Veteran Points: 228 More actions · Answer 9

Can post when i have an issue with notepad?

(sorry - worked late, feeling rude today)

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 10

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 !

Jpotucek SSCoach Points: 18583 More actions · Answer 11

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

Jpotucek SSCoach Points: 18583 More actions · Answer 12

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

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 13

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'

Jpotucek SSCoach Points: 18583 More actions · Answer 14

Jpotucek

SSCoach

Points: 18583

December 7, 2006 at 8:16 am

#676627

awesome! THanks