使用Oracle中的索引反转字符串 [英] Reversing a string using an index in Oracle

问题描述

我有一个包含ID和字符串的表，我需要能够正确索引以搜索字符串的结尾。我们当前如何处理它是将信息复制到另一个表并反转每个字符串并正常索引它。我想做的是使用某种允许反向搜索的索引。

I have a table that has IDs and Strings and I need to be able to properly index for searching for the end of the strings. How we are currently handling it is copying the information into another table and reversing each string and indexing it normally. What I would like to do is use some kind of index that allows to search in reverse.

示例

Data: 
F7421kFSD1234 
d7421kFSD1235 
F7541kFSD1236 
d7421kFSD1234
F7421kFSD1235 
b8765kFSD1235 
d7421kFSD1234

我们的用户通常输入他们的搜索方式是......

The way our users usually input thier search is something along the lines of...

* 1234

通过反转字符串（以及搜索字符串：4321 *）我可以找到我要找的内容而无需完全扫描整个字符串表。我的问题是：制作第二张桌子是最好的方法吗？

By reversing the strings (and the search string: 4321*) I could find what I am looking for without completely scanning the whole table. My question is: Is making a second table the best way of doing this?

有没有办法扭转指数？
我试过像这样的索引...

Is there a way to reverse index? Ive tried an index like this...

create index REVERSE_STR_IDX on TABLE(STRING) REVERSE;

但根据解释计划，oracle似乎没有使用它。

but oracle doesn't seem to be using it according to the Explain Plan.

预先感谢您的帮助。

更新：

我确实遇到了unicode字符无法正确反转的问题。对此的解决方案是投射它们。

I did have a problem with unicode characters not being reversed correctly. The solution to this was casting them.

示例：

select REVERSE(cast(string AS varchar2(2000)))
from tbl 
where id = 1

推荐答案

有一个神话，可以使用反向键索引，但是，我从未见过这种情况。

There is the myth that a reverse key index can be used for that, however, I've never seen that in action.

我会尝试基于手动函数的索引。

I would try a "manual" function based index.

CREATE INDEX REVERSE_STR_IDX on TBL(reverse(string));

SELECT *
  FROM TBL
 WHERE reverse(string) LIKE '4321%';

这篇关于使用Oracle中的索引反转字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！