Apostrophe in the data.

Question

Apostrophe in the data.

roy.tollison

SSCommitted

Points: 1930
More actions
April 7, 2008 at 1:51 pm

#186123

select * from table1
where ((col1+col2+col3) in (select (col1+col2+col3) from table2))
my col3 unfortunately contains the apostrophe. how can i get it to work correctly?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

roy.tollison (4/7/2008)
select * from table1 where ((col1+col2+col3) in (select (col1+col2+col3) from table2))
my col3 unfortunately contains the apostrophe. how can i get it to work correctly?

have you tried this:

select

*

from

dbo.table1 t1

inner join dbo.table2 t2

on (t1.col1 = t2.col1

and t1.col2 = t2.col2

and t1.col3 = t2.col3)

😎

GSquared SSC Guru Points: 260824 More actions · Answer 2

roy.tollison (4/7/2008)
select * from table1 where ((col1+col2+col3) in (select (col1+col2+col3) from table2))
my col3 unfortunately contains the apostrophe. how can i get it to work correctly?

Are you saying that the column name contains an apostrophe? If so, you can use square braces around it. If the data contains an apostophe, that shouldn't matter.

Also, as an aside, queries like the above generally are very, very slow. If you can, re-write it as a join.

select table1.*

from table1

inner join table2

on table1.col1 = table2.col1

and table1.col2 = table2.col2

and table1.col3 = table2.col3

If that will result in row-multiplication, then use that to generate the "in" sub-query.

select *

from table1

where id in

(select table1.id

inner join table2

on table1.col1 = table2.col1

and table1.col2 = table2.col2

and table1.col3 = table2.col3)

Either one will almost certainly be faster than string concatenation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

roy.tollison SSCommitted Points: 1930 More actions · Answer 3

Not sure i can get away with using an inner join command but i will see what happens.

I am using(forced) a third party query builder. the main query is already built and unchangable from program. if i send it another non-blank parameter such as the

((col1+col2+col3) in (select (col1+ col2+col3) from table2))

then it will automatically/only assume that to be a where clause so it will append it to the t-sql as 'where ....'

roy.tollison SSCommitted Points: 1930 More actions · Answer 4

actually make that extra parameter added on as 'and ....'

the where clause is already built and added into the t-sql all i am able to add is 'and ...' to help it shorten the query time.

GSquared SSC Guru Points: 260824 More actions · Answer 5

Try making the appostrophe a double-apostrophe.

Also, test the thing for SQL injection, just for your own sanity/safety.

Try using "1=1; select * from sys.all_objects --" as the string you send it. See if it at least rejects something that blatant. The semicolon and the double-dash at the end are important for this test. (It you're not sure what I'm talking about, search for "SQL injection" online.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

Why not replace all the apostrophes andtrim the columns, for comparison. Also, I agree that the inner join would be much better, if applicable.

DECLARE @STR VARCHAR(10)

SET @STR = 'ADAM''S'

SELECT REPLACE(@STR,'''','')

My blog: http://jahaines.blogspot.com

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Try QuoteName. From BOL:

Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server 2005 delimited identifier.
Transact-SQL Syntax Conventions
Syntax
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
Arguments
' character_string '
Is a string of Unicode character data. character_string is sysname.
' quote_character '
Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used

So it would be like QuoteName(col1, '''') + QuoteName(col2, '''') etc... Those are 4-single quotes

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

roy.tollison SSCommitted Points: 1930 More actions · Answer 8

i am sorry for all the confusion BUT relying on what i was told and not on what i knew the error wasn't with an apostrophe but actually looked like this...

Incorrect syntax near ')'

so they kept saying incorrect syntax near the apostrophe. well i should have known better. sorry for the waste of your time. next time i will verify what i am told is the error.