比较Oracle中的字符串 [英] Compare Strings in Oracle

问题描述

我需要查询一个表给出一个字符串的值。表是区分大小写的，但我想在比较中做一个ToLower（）。

假设我有一个包含以下数据的类表。

  class teacher 
 ----------------- 
 Mat101 Smith 
 MAT101 Jones 
 mat101 Abram 
 ENG102 Smith 
 

我的查询应该是

 选择教师从下层（类）='math101'的课程
  

最好的方法来做比较？

更新

我不能控制数据库或数据。我是只读消费者。

解决方案

否;最好改进数据：创建一个数字ID，表示类的看似无意义的变体（以及可能是关联的查找表来获取ID）。使用where子句中的ID列，您应该使用索引的数字列。

如果没有选项，请考虑基于函数的索引

如果这不是一个选项，并且最好的问题与性能严格相关，请考虑反规范化并添加一个包含较低（类）

如果这不是一个选项，更新数据，使其全部为小写（并采取措施插入/更新只有小写类数据）。

如果你不能更新这样的数据，那么答案是也许。

如果您尚未测试该列的索引，则无法调用它。

I need to query a table for values given a string. The table is case sensitive but I want to do a ToLower() in the comparison.

Suppose I have a classes table with the following data.

class    teacher
-----------------
Mat101   Smith
MAT101   Jones
mat101   Abram
ENG102   Smith

My query should be something like

Select teacher From classes where lower(class) = 'math101'

Is this the best way to do the comparison?

Update

I have no control over the database or the data. I am a read only consumer.

解决方案

No; it would be better to improve the data: create a numeric ID that represents these seemingly meaningless variations of class (and probably an associated lookup table to get the ID). Use the ID column in the where clause and you should be hitting an indexed numeric column.

If that's no an option, consider a function-based index on lower(class).

If that's not an option, and the question of "best" is strictly relative to performance, consider denormalizing and adding a column that contains lower(class), probably populated with a trigger.

If that's not an option, update the data so that it's all lowercase (and take measures to insert/update only lowercase class data).

If you can't update the data like that, then the answer is "maybe".

In any case, you can't call it best if you haven't tested indexing of the column.

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