Oracle 列别名中的双引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/56591/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Double Quotes in Oracle Column Aliases
提问by Christopher McAtackney
Ok, this is bit of an obscure question, but hopefully someone can help me out with it.
好吧，这是一个有点晦涩的问题，但希望有人能帮我解决这个问题。
The system I'm working on builds a dynamic SQL string for execution inside a stored procedure, and part of that dynamic SQL defining column aliases, which themselves are actually values retrieved from another table of user generated data.
我正在开发的系统构建了一个动态 SQL 字符串，用于在存储过程中执行，以及定义列别名的动态 SQL 的一部分，它们本身实际上是从用户生成的数据的另一个表中检索的值。
So, for example, the string might look something like;
因此，例如，字符串可能看起来像；
SELECT table1.Col1 AS "This is an alias" FROM table1
This works fine. However, the value that is used for the alias can potentially contain a double quote character, which breaks the outer quotes. I thought that I could maybe escape double quotes inside the alias somehow, but I've had no luck figuring out how to do so. Backslash doesn't work, and using two double quotes in a row results in this error;
这工作正常。但是，用于别名的值可能包含双引号字符，这会破坏外部引号。我认为我可以以某种方式在别名中转义双引号，但我没有弄清楚如何这样做。反斜杠不起作用，连续使用两个双引号会导致此错误；
SQL Error: ORA-03001: unimplemented feature
03001. 00000 - "unimplemented feature"
*Cause: This feature is not implemented.
Has anyone had any experience with this issue before? Cheers for any insight anyone has.
有没有人以前有过这个问题的经验？为任何人的任何见解干杯。
p.s. the quotes are needed around the aliases because they can contain spaces.
ps 别名周围需要引号，因为它们可以包含空格。
采纳答案by ibz
Can you just put another character instead of double quotes and replace that with double quotes in the code?
您可以在代码中放置另一个字符而不是双引号并将其替换为双引号吗？
Something like this:
像这样的东西：
SELECT table1.Col1 AS "This is |not| an alias" FROM table1
Then just replace | with ".
然后只需替换 | 和 ”。
I know it's a hack, but I can't think of any better solution... And what you are doing there is a hack anyway. The "nice" way would be to select the values and the column names separately and associate them in your code. That would make things much cleaner.
我知道这是一个黑客，但我想不出任何更好的解决方案......无论如何你在做什么都是一个黑客。“不错”的方法是分别选择值和列名，并将它们关联到您的代码中。那会让事情变得更干净。
回答by swissunix
use the Oracle quote operator:
使用 Oracle 报价运算符：
select q'#someone's quote#' from dual;
the '#' can be replaced by any character
'#' 可以被任何字符替换
回答by JosephStyons
When I run this:
当我运行这个：
select 'test"columnname"' from dual
Oracle returns this (notice the Oracle-generated column name):
Oracle 返回这个（注意 Oracle 生成的列名）：
'TESTCOLUMNNAME'
--------------------------------
test"columnname
The fact that Oracle's column name doesn't include my double-quote tells me that Oracle probably cannot represent that.
Oracle 的列名不包含我的双引号这一事实告诉我，Oracle 可能无法表示这一点。
Best bet as far as I can see is to strip double-quotes from your data prior to using column names. Sadly, that will also require that you do the same filtering when you selectthose columns, but I don't see another way.
据我所知，最好的办法是在使用列名之前从数据中去除双引号。可悲的是，这也要求您在选择这些列时进行相同的过滤，但我没有看到其他方式。
回答by EvilTeach
a possibly fruitful area of investigation would be to look into the quote method.
一个可能富有成效的调查领域是研究引用方法。
my $quotedString = $dbh->quote( $string );
我的 $quotedString = $dbh->quote( $string );
回答by Artem
Try this, two single quotes actually look like one double quote in output:select 1 as "University ''John Smith''" from dual;
试试这个，两个单引号在输出中实际上看起来像一个双引号：select 1 as "University ''John Smith''" from dual;
