问题描述
假设我的列值为aaa'gh
,它将在oracle中引发错误,提示sql命令未正确结束.
Suppose I have a column value as aaa'gh
it will throw error in oracle saying sql command not properly ended.
我的问题是,如果我不知道我的值中有多少'
,该如何安全地逃脱它们.
My question is if I don't know how many '
are in my value, how can I escape them safely.
推荐答案
最好的方法是使用 引用字符串文字技术 .语法为q'[...]'
,其中"["和]"字符可以是以下任意字符,只要它们尚未出现在字符串中即可.
The best way is to use the quoting string literal technique. The syntax is q'[...]'
, where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.
- !
- []
- {}
- ()
- < >
您不必担心字符串中的单引号.
You don't have to worry about the single-quotation marks within the string.
因此您可以简单地将SQL编写为
So you could simply write the SQL as,
SELECT q'[aaa'gh]' FROM DUAL;
它为开发人员节省了大量时间.过去,我们(开发人员)用来在开发DB中使用dbms_output验证动态sql的日子已经一去不复返了,只是为了确保在投入生产之前一切都准备就绪.
It saves a lot of time for developers. Gone are those days when we(developers) used to verify the dynamic sql using dbms_output in development DB, just to make sure things are at place before moving into production.
这篇关于如何在Oracle中预测和转义单引号'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!