Monday, February 13, 2012

apostrophes, etc. in WHERE statements?

How do I handle and apostrophes and other punctuation in stored procedure Sql statements:

SELECT L_ID, L_Name
FROM tblHVACContractors
WHERE (L_Name = 'Mare's Heating & Cooling Services')

You have to double it to escape it.

SELECT

L_ID, L_Name

FROM

tblHVACContractors

WHERE

(L_Name='Mare''s Heating & Cooling Services')

|||

Yup, that will work if we know the value before hand, but what if we are using parameters. Is there a replace function in SQL?

bullpit

|||

Bullpit has better worded my situation.

|||

It depends on where the data is coming from. If your application is sending the data with special characters like that and you are using parameterized queries, you dont need to do any additional coding. The parameterized queries will take care of it. If you are concatenating value as

" SELECT .. FROM WHERE col1 = " + sometxtbox.text

then you have to do a replace on the value in the text. which is one more reason why you should use parameterized queries, if you are not already coding that way.

|||

That works.

bullpit

No comments:

Post a Comment