Skip to main content
N
NettlesD
Inspiring
November 18, 2009
Question

<cfqueryparam and passing a value with "&"

  • November 18, 2009
  • 2 replies
  • 1129 views

I'm trying to pull values out of an Oracle database where one of the columns has a value of "KA&S".

Using SQL Plus, Toad or whatever, you have to code the statement like this:

select *
  from department where deptid = 'KA'||'&'||'S'

The statement below will return 0 records with the value of KA&S.

select *
  from department where deptid = <cfqueryparam value="KA&S" cfsqltype="cf_sql_varchar" maxlength="10">

Anyone have any idea on how to format the "KA&S" to where the <cfqueryparam> tag will pass the value as 'KA'||'&'||'S' so that Oracle can execute the SQL statement correctly?

    This topic has been closed for replies.

    2 replies

    J
    Jochem van Dieten
    Inspiring
    November 18, 2009

    What is the datatype in your Oracle database? What is the character set?

    N
    NettlesDAuthor
    Inspiring
    November 18, 2009

    Those are PeopleSoft tables so I'm not sure.  As I said before, you must concatenate the "&" in any PL/SQL code within an SQL statement.  I know that for a fact.

    UPDATE:  Ok, it looks like the <cfqueryparam> is handling it correctly and returning the records.  So, I don't have to escape the "&" after all.  But, as I said before, you must concatenate the "&" in any PL/SQL code within an SQL statement.  I know that for a fact.  I got bit on that one time.

    Thanks everyone.

    A
    Adam Cameron.
    Inspiring
    November 18, 2009

    Those are PeopleSoft tables so I'm not sure.  As I said before, you must concatenate the "&" in any PL/SQL code within an SQL statement.  I know that for a fact.

    Are you sure that's not just a vagary of the way the client works?  Similar to the way you need to stick a semi-colon at the end of your SQL statements in TOAD etc, but do not when passing the SQL via a JDBC connection?

    I have worked on some some moderate-sized Oracle sites, and have never come across this.

    --

    Adam

    ilssac
    ilssac
    Inspiring
    November 18, 2009

    First of all, what do you need to do that?

    I have never had trouble with a database and the ampersand '&' charcter as data.

    I could see problems if this was a column name or some such, but not as part of some string data like that.

    N
    NettlesDAuthor
    Inspiring
    November 18, 2009

    Executing this statement against our Oracle database will return 0 records.  I can assure you that.

    select *
      from department where deptid = <cfqueryparam value="KA&S" cfsqltype="cf_sql_varchar" maxlength="10">

    Any statement I execute against our Oracle database using the "&" as a value has to be formatted as I described.  I have several PL/SQL procedures that have to have the same format appled to them if using an "&" in the input value.  No way around that on our system.

    select *
      from department where deptid = 'KA'||'&'||'S'

    So, the question is, how do I turn KA&S into KA'||'&'||'S so that the <cfqueryparam> will accept it and execute the statement and return the records?

    A
    Adam Cameron.
    Inspiring
    November 18, 2009

    I too am sceptical of what you say, but however.

    Anyhow, sounds like you want to REPLACE "&" with "'||'&'||'", yes?

    --

    Adam

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices