how to select a string with single quotes within it 3

[daglugub37]

to find notes
select *
from notes
where [text] = 'notes'

but how do i find no't'es

I tried
select *
from notes
where [text] = 'no't'es'
0 hits

I tried
select *
from notes
where [text] = "no't'es"
0 hits

I know the value exists so I must have the query wrong

 

[bborissov]

select *
from notes
where [text] = 'no''t''es'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[DotNetGnat]

select *
from notes
where [text] = 'no''t''es'

-DNG

 

[bborissov]

All single quotes that are included in string must be doubled.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mhoyt]

Hmm, I can't get this to work.

Per BOL
Embedding apostrophes If the data you are searching for contains a single quotation mark (an apostrophe), you can enter two single quotation marks to indicate that you mean the single quotation mark as a literal value and not a delimiter. For example, the following condition searches for the value "Swann's Way":
='Swann''s Way'

So I wanted to test this, but I can't even INSERT the text with apostraphe into the test table

USE SANDBOX

CREATE TABLE TBLTEST 
	(FIELD1 VARCHAR(10)
	,FIELD2 VARCHAR(10)
	)

INSERT INTO TBLTEST VALUES ('JOE','NOTE')
INSERT INTO TBLTEST VALUES ('RICK,'NOTE"'"S')

SELECT * FROM TBLTEST

Every combination I have tried for the second INSERT line fails for syntax. How do you get an apostraphe in?

 

[DotNetGnat]

INSERT INTO TBLTEST VALUES ('RICK,'NOTE''S')

-DNG

 

[bborissov]

As I said just double single quotes inside string:

'Note''S'

These inside are 2 sigle quotes not one double quote.
If you copy and paste this code in QA you will find the diffrence.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mhoyt]

OK, now I've got it. Thanks for the help. The following works....

USE SANDBOX

CREATE TABLE TBLTEST 
	(FIELD1 VARCHAR(10)
	,FIELD2 VARCHAR(10)
	)

INSERT INTO TBLTEST VALUES ('JOE','NOTE')
INSERT INTO TBLTEST VALUES ('RICK','NOTE''S')

SELECT * FROM TBLTEST

SELECT * FROM TBLTEST
WHERE [FIELD2] LIKE('%''%')
--Single apostrophe is represented by two single quote marks

 

[daglugub37]

thanks all, I understand now

 

[Gooser]

You could also try something like:

declare @tick char=char(39) --char(39) is the ASCII value of '
select *
from notes
where [text] = 'no' + @tick + 't' + @tick + 'es'

using char(39) as a stand-in for its counterpart '

slightly more verbose, but certainly more maintainable than trying to escape ' with ' and ending up with '''' meaning ' --believe me, I've seen this, and RECENTLY

They actually did it something like this:

DECLARE @SINGLE_QUOTE char

...

--BUNCHA LINES OF CODE OMITTED--

...

SET @SINGLE_QUOTE = ''''

then they used @SINGLE_QUOTE in their code. Because @SINGLE_QUOTE is quick-and-easy to type, right? Dummy.

This is much easier to keep straight programmatically than escaping your ''s (<-read "tick's").

Just make sure you comment what char(39) is and you should be fine.

v/r
Gooser

 

[mhoyt]

What if you want to find any instance of a value with an apostophe?