Database table column name contains # sign

[kfnavara]

I need to query a database table (I cannot rename the column names) in where the column name contains a # sign (PRICE_OR_DISC#1). This causes CF to crash because of the # sign. Is there a way to query a column name that contains a # sign?

[Owainnorth]

Double-hash escapes a hash within CF.

[kfnavara]

Yes, I tried that but I need to keep one hash (#) because it is part of the column name. Thanks!

[Dan_Bracuk]

Your post is too vague.  What did you try and what was the result?

[kfnavara]

I have to query an SQL server database table that I do not administer and the table column/field name cannot be changed. The column/field name I need to pull data from includes a # sign in the column/field name (PRICE_OR_DISC#1). See query below.

When I run the query, ColdFusion errors because there is a single # sign encountered. Being that I cannot change the column/field name to remove the # sign I am trying to find if there is a workaround for this situation. Does this make more sense?

  <cfquery name="MyQuery" datasource="MyDataSource">
   SELECT
    ib.ID_Item,
    ib.DESCR_1,
    ib.DESCR_2,
    ib.CODE_CAT_PRDT,
    tpi.PRICE_OR_DISC#1 AS Price,
    tpi.PREC_PRICE
     
   FROM MyDB ib

   INNER JOIN TABLES_PRICE_ITEM tpi ON ib.ID_Item = tpi.ID_Item

   WHERE
    ib.ID_Item = '#form.ID#'
   </cfquery>

Thanks for your help!

[Reed_Powell-ttnmOb]

Have you tried putting the column name in square brackets, as well as doubling up the hash-marks?  The

square brackets are for SQL because of the special character in the name (or when the name is a SQL reserved word).  The doubled-up hashmarks are for CF so that it will pass the single hashmark to SQL. 

-reed

[-__cfSearching__-]

>> ColdFusion errors because there is a single # sign encountered.

Right. That is why Owain suggested you escape the # sign by using two # signs, not just one. Did you try that, because it is not in your sql code?

[kfnavara]

My apologies and thanks to all. I tried "tpi.PRICE_OR_DISC##1," again, which I was positive that tried more than once and it works now. Obviously operator error on my part.

Thanks so much for helping me to the solution!