0

In a unix script I am calling below SQL which contains a value for the column company_name with & symbol: 'SK&CO'

and other value(city,date) I am passing through script

select * from stock_details where company_name ='SK&CO' --value hardcoded 
and city = &2 
and date = &1;

How do I keep the & (ampersand) in the SQL with other substitute variable values?

I have tried set define off, which is not working as I need to use other substitute variable values

set define off, will escape all the substitute values where I want to ignore substitution for the single & and treat this as a value to show the result output with matching values.

sabya
  • 109
  • 2
  • 4
  • 20
  • @Bohemian , the linked question doesn't contains answer for this question . – sabya Feb 20 '17 at 10:53
  • sure it does: `where company_name ='SK&' || 'CO'`. Does that not work? – Bohemian Feb 20 '17 at 10:55
  • Thanks ..working. – sabya Feb 20 '17 at 11:30
  • @Bohemian - Congrats for pointing to an inefficient hack, instead of pointing to the correct solution. Good luck maintaining code like that. –  Feb 20 '17 at 13:18
  • @sabya - the linked question does contain answers for your question. One is the third answer in that thread (not the accepted answer, and not anything to do with concatenation). Specifically, in SQL\*Plus you can change the character used to "mark" substitution variables from `&` to any other character. For example, to change it to `~` (assuming you don't need THAT character in your strings): `SET DEFINE ~`. Another correct answer in that thread: `SET ESCAPE ON` and then you can use \ to escape the `&`. `&var` will still be a substitution variable, but `\&var` will be expanded to `&var`. –  Feb 20 '17 at 13:24
  • @a_horse_with_no_name - I see some confusion in an answer that is now deleted. First, substitution variables themselves are **not** an SQL concept, they are only in SQL\*Plus. (They are **substitution** variables.) Second, the slash for escaping **does** work in SQL\*Plus, you just need to `SET ESCAPE ON` (the default is `OFF` - if you want it `ON` when you start a session, you can put `set escape on` in `login.sql`.) –  Feb 20 '17 at 13:29
  • @mathguy nevertheless, this question is a dupe of the other and, further, we have proof that the other question has an (accepted) answer that answers this question. If you don't like the answers there, you are free to vote up or down, comment or post your own (different) answer there – Bohemian Feb 20 '17 at 16:48
  • @bohemian - Certainly, that's one way to respond to my comment. Another is "yes, I see your point, I agree both `set define ~` and `set escape on` are better answers than splitting and concatenation - with a little more time spent on that thread I would also have offered them as the right solutions." Which answer to choose between these two is a choice I can't make for you. –  Feb 20 '17 at 22:19

0 Answers0