1

I'm creating a query to send data to a vendor and need to set the column names to match their specs. I can set the column names for most of the columns; however, a few of the fields have '&' included in their column name. When running the query the ampersand character in the column alias name is causing a substitution variable dialog box to be displayed. While this is the normal behavior when using the ampersand character, are there any options to suppress the behavior if the ampersand is included in the column alias name?

Ideally, I would the query to run without displaying the dialog box and to include the ampersand character in the column name on the output.

Examples

PAY_DED_CODE as "AD&D CODE",

PAY_DED_CODE_EFF_DATE as "AD&D EFFECTIVE DATE",

PAY_DED_CODE_STATUS as "AD&D STATUS",

PAY_DED_CODE_AMOUNT as "AD&D DEDUCTION AMOUNT"
HDM
  • 23
  • 3
  • 2
    Does this answer your question? [How do I ignore ampersands in a SQL script running from SQL Plus?](https://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus) – padaleiana Aug 31 '20 at 20:32
  • The options will depend on the client. **Displaying the dialog box"* suggests some GUI tool. Perhaps you are using PL/SQL Developer, SQL Developer, or Toad? In PL/SQL Developer, there is a `&` icon at the bottom of a SQL Window. Obviously telling you this is pointless if you are using something else. – William Robertson Aug 31 '20 at 22:01

1 Answers1

0

I would STRONGLY advise NOT doing it this way. Then you will be masking everywhere else you want to work with that column. Same with having embedded spaces in the column names, just not a good idea.

Instead, I would put that in the UI display output to the user and create your screen to show the titles pre-defined as you want them to appear. Or if the columns of the data output are auto-generated, I would have a routine that would fix them based on some naming convention / pattern.

Ex: If the column name has "AD_D", then change to "AD&D "

You could also code to handle "CamelCasingStrings" to for "Camel Case Strings" such as where the upper case character indicates there should be a space to provide natural readability to the end-user.

But special characters in column names and spaces would mean you always have to wrap in code with quotes in places the future might not play nice with, especially like web-based output using other languages doing the presentations.

DRapp
  • 47,638
  • 12
  • 72
  • 142