Skip to main content
H
HoserDave
Participating Frequently
March 15, 2007
Question

One single tick in select string?

  • March 15, 2007
  • 3 replies
  • 450 views
I'm trying to run a select statement that uses a dynamic AND clause that looks like this:

AND b.txt_value = 'Things I can't do at home'

As you can see, I have a lone/solo single quote in the word can't. I tried preservesinglequotes but that didn't help (I believe you need matching single quotes for that). I can't change the data, so I'm hoping there's a CF function I'm not aware of that can fix this. The SQL error is of course:
ORA-00933: SQL command not properly ended

Many thanks in advance for any help anyone can provide!

Dave
    This topic has been closed for replies.

    3 replies

    N
    Newsgroup_User
    Inspiring
    March 15, 2007
    <cfquery>
    SELECT * FROM Table WHERE b.txt_value = <cfqueryparam
    cfsqltype="cf_sql_varchar" value="#trim(variables.txt_value)#">
    </cfquery>

    That is by far the better option, but if one can not use the
    <cfqueryparam ...> tag, there are limitations at this time. The
    preserverSingleQuotes() function is also built to handle this situation.
    If I am not getting this backwards it would be.

    WHERE b.txt_value = '#preserveSingleQuotes(trim(variables.txt_value))#'>
    N
    Neo Rye
    Inspiring
    March 15, 2007
    <cfset variables.txt_value = "Things I can't do at home">

    <cfquery>
    SELECT * FROM Table WHERE b.txt_value = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(variables.txt_value)#">
    </cfquery>
    D
    Dan_Bracuk
    Inspiring
    March 15, 2007
    quote:
    Originally posted by: HoserDave
    I'm trying to run a select statement that uses a dynamic AND clause that looks like this:

    AND b.txt_value = 'Things I can't do at home'

    As you can see, I have a lone/solo single quote in the word can't. I tried preservesinglequotes but that didn't help (I believe you need matching single quotes for that). I can't change the data, so I'm hoping there's a CF function I'm not aware of that can fix this. The SQL error is of course:
    ORA-00933: SQL command not properly ended

    Many thanks in advance for any help anyone can provide!

    Dave

    cfqueryparam should take care of it.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices