我试着比较有空间和没有空间的字符串

我首先假设添加了oracle字符串比较.

select case when 'a ' > 'a' then 't' else 'f' end from dual; 

enter image description here

select case when 'a ' < 'a' then 't' else 'f' end from dual; 

enter image description here

select case when 'a ' = 'a' then 't' else 'f' end from dual; 

enter image description here

推荐答案

[TL;DR]要使其按预期工作,请使用CAST为文本提供显式数据类型.比如CAST('a ' AS VARCHAR2(10))CAST('a ' AS CHAR(2)).


默认的文本数据类型出现了问题,似乎是一个bug.

如果定义一个表:

CREATE TABLE data (lhs CHAR(2), rhs CHAR(1));

INSERT INTO data VALUES ('a ', 'a');

然后运行:

select case when lhs > rhs then 't' else 'f' end AS gt_ch,
       case when lhs < rhs then 't' else 'f' end AS lt_ch,
       case when lhs = rhs then 't' else 'f' end AS eq_ch,
       case
       when lhs > rhs then 'gt'
       when lhs < rhs then 'lt'
       when lhs = rhs then 'eq'
       else NULL
       end AS comp,
       case
       when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS gt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS lt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS eq_vc,
       DUMP(lhs) AS dmp_lsh_raw,
       DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
       DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
       DUMP(rhs) AS dmp_rsh_raw,
       DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
       DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from   data;

那么输出是:

GT_CH LT_CH EQ_CH COMP GT_VC LT_VC EQ_VC DMP_LSH_RAW DMP_LHS_CH DMP_LHS_VC DMP_RSH_RAW DMP_RHS_CH DMP_RHS_VC
f f t eq t f f Typ=96 Len=2: 97,32 Typ=96 Len=2: 97,32 Typ=1 Len=2: 97,32 Typ=96 Len=1: 97 Typ=96 Len=2: 97,32 Typ=1 Len=1: 97

这是有效的,将它们作为默认数据类型CHAR进行比较,使用Blank-Padding Comparison Semantics,那么'a ''a'是相等的,但将它们作为VARCHAR2个数据类型进行比较,使用非填充比较语义,那么'a '大于'a'.这是预期的行为.

如果对子查询分解子句(也称CTE)中生成的CHAR种数据类型的文本执行相同的查询:

WITH cte (lhs, rhs) AS (
  SELECT CAST('a ' AS CHAR(2)),
         CAST('a' AS CHAR(1))
  FROM   DUAL
)
select case when lhs > rhs then 't' else 'f' end AS gt_ch,
       case when lhs < rhs then 't' else 'f' end AS lt_ch,
       case when lhs = rhs then 't' else 'f' end AS eq_ch,
       case
       when lhs > rhs then 'gt'
       when lhs < rhs then 'lt'
       when lhs = rhs then 'eq'
       else NULL
       end AS comp,
       case
       when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS gt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS lt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS eq_vc,
       DUMP(lhs) AS dmp_lsh_raw,
       DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
       DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
       DUMP(rhs) AS dmp_rsh_raw,
       DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
       DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from   cte;

然后,输出与数据存储在表中时相同,这也是预期的行为.

但是,如果删除显式强制转换并使用文本的默认数据类型:

WITH cte_default_data_type (lhs, rhs) AS (
  SELECT 'a ',
         'a'
  FROM   DUAL
)
select case when lhs > rhs then 't' else 'f' end AS gt_ch,
       case when lhs < rhs then 't' else 'f' end AS lt_ch,
       case when lhs = rhs then 't' else 'f' end AS eq_ch,
       case
       when lhs > rhs then 'gt'
       when lhs < rhs then 'lt'
       when lhs = rhs then 'eq'
       else NULL
       end AS comp,
       case
       when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS gt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS lt_vc,
       case
       when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
       then 't'
       else 'f'
       end AS eq_vc,
       DUMP(lhs) AS dmp_lsh_raw,
       DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
       DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
       DUMP(rhs) AS dmp_rsh_raw,
       DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
       DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from   cte_default_data_type;

那么输出是:

GT_CH LT_CH EQ_CH COMP GT_VC LT_VC EQ_VC DMP_LSH_RAW DMP_LHS_CH DMP_LHS_VC DMP_RSH_RAW DMP_RHS_CH DMP_RHS_VC
f f f eq t f f Typ=96 Len=2: 97,32 Typ=96 Len=2: 97,32 Typ=1 Len=2: 97,32 Typ=96 Len=1: 97 Typ=96 Len=2: 97,32 Typ=1 Len=1: 97

前3CASE个表达中没有一个是正确的(如问题所示).然而,DUMP的输出与前面的查询相同.

这种情况不应该发生,因为这些值要么作为CHAR个数据类型进行比较,在这种情况下,应该应用空白填充比较语义,要么作为VARCHAR2个数据类型进行比较,在这种情况下,应该应用非填充比较语义,在这种情况下,它似乎在使用其他内容,不遵循任何规则,并且不是预期的行为.

为了解决这个问题,使用CAST为文本提供一个显式的数据类型,因为这似乎"解决"了这个问题.

db<>fiddle 100

Sql相关问答推荐

SQL(PostgreSQL)从条件创建点表

在postgres中动态计算出现次数并插入到json中

使用sede获取不一定有两个不同标签的所有问题

分组多输出访问查询问题

使用WHERE子句进行筛选时,SQL SELECT查询返回总计数

我希望以正确的升序获取SQL结果.怎样才能得到它们?

返回找到的最小和最大row_number()spark SQL

查询每周数据(周一至周日),避免年度日期重叠

明细表中没有记录如何更新主表的值为0

如何简化此PostgreSQL查询以计算平均值?

将用户授予另一个用户不授予权限

SQL JSON_QUERY 使用列中的值构造 json 路径并接收错误

将时间戳四舍五入到最近 10 分钟的查询

如何 for each id创建长度等于id长度的不同日期序列?

基于字符串的SQL查询

在给定的日期范围内填写缺失的日期

在 postgresql 中保存带有时间戳的几何类型数据

SQL Group By 然后映射出是否存在值

删除具有相同 ID 的重复记录 - Postgresql

如何列出 Oracle 数据库中的所有函数并按修改日期排序