Search table by field that includes a quote

[TalentedFool]

Hi all

I'm trying to do a search on a table something like this

select * from table where field = 'this is the text's that I am searching for'


Obviously it throws it out due to single quote in the text. Noob question - how do I get around that?

Cheers

~ Remember - Nothing is Fool Proof to a Talented Fool ~

 

[TalentedFool]

Sorry, I found the solution to the above .. what I now need it get around this using a sub query. ..

select * from table where field = select field2 from table2

Field2 might contain a single quote in it such as a '

Does that make sense?

It throws the quoted string not terminated properly at me

~ Remember - Nothing is Fool Proof to a Talented Fool ~

 

[lewisp]

Put two single quotes together in the sting:

where field = 'this is the text''s that I am searching for'

 

[lewisp]

For your second enquiry, you need a join or a sub-query

SELECT a.*
FROM   table  a,
       table2 b
WHERE a.field = b.field2;[code]

or

[code]SELECT *
FROM   table
WHERE  field = (SELECT field2
                FROM   table2);

Note the second solution above assumes you have only 1 row in table2. If not, change the = to IN.

 

[TalentedFool]

Lewisp

I understand all that.

Sorry, this is not for me but a colleague and I've just had all the info. I might be in the wrong forum

what they are trying to do is on an ASP.net page is to build a string with a select command and run that against the database.

What he is trying to do is

string = 'select * from table1 where field = '" & searcher & "'"

Searcher might contain a single quote which is what is throwing him out

If you don't have a quick answer then I'll take this is a ASP.net forum

Thanks


~ Remember - Nothing is Fool Proof to a Talented Fool ~

 

[lewisp]

I think you will simply need to find a way to pass two single quotes where there is one within a string.

 

[engineer2100]

string = "select * from table1 where field = '" & searcher & "'"

HTH

-Engi

 

[TalentedFool]

Engineer. Thats - but that totally doesn't help

If searcher contains a single quote e.g "My searche's" it'll throw it out.

FYI - I found the solution. Since it was all done in VB.net I simply used the replace function so it now looks like

string = "select * from table1 where field1 = '" & replace(searcher,"'","''") & "'"

Works a treat! After that I scrapped it and started using parameters which, I'm not even going to get into.

Anyway - thanks for looking by ...

Subject Closed.

~ Remember - Nothing is Fool Proof to a Talented Fool ~