Oracle SQL 按其 ascii 值搜索或查找字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15380115/
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
Oracle SQL Search for or find character by its ascii value
提问by Shogoot
My challenge is to find certain characters across multiple tables.
我的挑战是在多个表中找到某些字符。
At my work the DB is set up to keep all date values in columns named like this d_date, d_inserteby_id, d_valid_to etc. But underscore is a reserved char in SQL right, so a straight forward like '%D_%' wont work.
在我的工作中，数据库被设置为将所有日期值保留在像这样命名的列中 d_date、d_inserteby_id、d_valid_to 等。但下划线是 SQL 中的保留字符，所以像 '%D_%' 这样的直截了当是行不通的。
So when im looking for rows containing a reference to a date i have to be a bit more creative then '%D_%'
因此，当我查找包含对日期的引用的行时，我必须比 '%D_%' 更有创意
I know i can:
select * from table t where Upper(T.column) like '%D_%' escape '\'
我知道我可以：
select * from table t where Upper(T.column) like '%D_%' escape '\'
But how to search for that d and underscore using its ascii value?
但是如何使用它的 ascii 值搜索那个 d 和下划线？
回答by David Aldridge
You can't search for a column name in the table that it's part of, you have to look at the data dictionary and use dynamic SQL if your schema changes frequently.
您不能在它所属的表中搜索列名，如果您的架构经常更改，您必须查看数据字典并使用动态 SQL。
You can ...
你可以 ...
select table_name,
column_name
from all_tab_columns
where owner = ? -- insert name of table owner
and upper(column_name) like 'D\_%' escape '\'
There's no advantage to using the ASCII value of D in the search -- it would just obfuscate the code. LIKE and ESCAPE is the correct approach.
在搜索中使用 D 的 ASCII 值没有任何好处——它只会混淆代码。LIKE 和 ESCAPE 是正确的方法。
Using mixed-case object names is considered a bad practice in Oracle, by the way.
顺便说一下，在 Oracle 中使用大小写混合的对象名称被认为是一种不好的做法。
Edit: of course if you really wanted to search for strings by their ASCII characters then you'd do something like:
编辑：当然，如果您真的想按 ASCII 字符搜索字符串，那么您可以执行以下操作：
where ascii(substr('D_123',1,1))=68 and
ascii(substr('D_123',2,1))=95
or
或者
where ascii(substr('D_123',1,1))||ascii(substr('D_123',2,1))='6895'
or
或者
where substr(dump(substr('D_123',1,2)),-6) = ' 68,95'
As ever, lots of ways to do things the wrong way.
与以往一样，很多方法都以错误的方式做事。
回答by Frank Schmitt
I'm still not sure why you want to use the ASCII value instead of the literal underscore, but perhaps this is what you want (95 is the ASCII code for '_'):
我仍然不确定为什么要使用 ASCII 值而不是文字下划线，但也许这就是您想要的（95 是 '_' 的 ASCII 代码）：
select *
from t
where upper(t.column1) like '%D\' || chr(95) || '%' escape '\'
Please note that this will raise an error if you try to replace the 95 with e.g. 65 (for an 'A'), since you cannot escape normal characters.
请注意，如果您尝试将 95 替换为例如 65（对于“A”），则会引发错误，因为您无法转义普通字符。
To handle this, you'd need some kind of logic to distinguish between normal characters and characters you have to escape.
为了解决这个问题，您需要某种逻辑来区分普通字符和必须转义的字符。
回答by TJ Abrahamsen
@Shogoot: If I understand you correctly, you want to be able to search through any tables that have any ROW of any column that has a VALUE of your search string.
@Shogoot：如果我对您的理解正确，您希望能够搜索任何具有搜索字符串值的任何列的任何 ROW 的表。
If the above is a correct assumption, I think your only solution is to use a block of PL/SQL, like shown here:
如果上述假设是正确的，我认为您唯一的解决方案是使用一块 PL/SQL，如下所示：
DECLARE
-- Find tables that have the right type of columns
CURSOR cur IS
SELECT atc.owner
,atc.table_name
,atc.column_name
FROM all_tab_columns atc
WHERE atc.data_type IN ('VARCHAR2', 'CHAR', 'DATE')
AND owner IN ('OE')
;
vTestString VARCHAR2(250) := CHR(45); -- CHR(45) = '-'
iCount INTEGER := 0;
vStatement VARCHAR2(2000) := '';
BEGIN
-- Loop through tables and columns
FOR rec IN cur LOOP
BEGIN
-- Find tables that have the right type of columns
vStatement := 'SELECT COUNT(*) '
|| 'FROM ' || rec.owner || '.' || rec.table_name || ' '
|| 'WHERE ' || rec.column_name || ' LIKE ''%' || vTestString || '%'' '
;
--dbms_output.put_line(vStatement);
EXECUTE IMMEDIATE vStatement
INTO iCount
;
IF (iCount > 0) THEN
dbms_output.put_line('Found ' || iCount || ' matches in: ' || rec.owner || '.' || rec.table_name || '.' || rec.column_name);
END IF;
EXCEPTION
-- Oops, we might end here if there is an error
WHEN OTHERS THEN
dbms_output.put_line('Something messed up with: ' || rec.owner || '.' || rec.table_name || '.' || rec.column_name || '(' || SQLERRM || ')');
END;
END LOOP;
END;
You will have to change the search string to whatever you need. The query above is based on Oracle's OE schema, and you will have to tweak the "cur" query to fit your needs.
您必须将搜索字符串更改为您需要的任何内容。上面的查询基于 Oracle 的 OE 模式，您必须调整“cur”查询以满足您的需要。
Also, keep in mind that you might search through a LOT of data, so use it based on your own comfort.
另外，请记住，您可能会搜索大量数据，因此请根据自己的舒适度使用它。
Hope this helps.
希望这可以帮助。
~ TJ
~ TJ
