在DB2中修剪Char列中的空白空间 [英] Trimming Blank Spaces in Char Column in DB2

问题描述

我正在尝试删除出现在DB2中的CHAR列中的空格。我收到一些帮助的函数 TRANSLATE ，以确定左包含以三个字母开头的记录：

I'm trying to remove blank spaces that appear in a CHAR column within DB2. I received some helped here with the function TRANSLATE to determine if Left contained records that began with three letters:

select pat.f1, hos.hpid, hos.hpcd
from patall3 pat
join hospidl1 hos on pat.f1=hos.hpacct
where TRANSLATE( 
LEFT( hos.hpid, 3 ),
'AAAAAAAAAAAAAAAAAAAAAAAAA', 
'BCDEFGHIJKLMNOPQRSTUVWXYZ'
 ) <> 'AAA'
order by pat.f1; 

但是正如你在我的截图中可以看到的，有记录仍然存在，可能是因为它们以空格处。我试过 cast（hos.hpid as varchar）但是不工作。是否可以修剪这些空格？

But as you can see in my screenshot, there are records that remain, presumably because they begin with a blank space. I tried cast (hos.hpid as varchar) but that doesn't work. Is it possible to trim these blank spaces?

感谢，

推荐答案

使用LTRIM （）在LEFT（）之前修剪空白。

Use LTRIM() or TRIM() to trim blanks before the LEFT()

select pat.f1, hos.hpid, hos.hpcd
from patall3 pat
join hospidl1 hos on pat.f1=hos.hpacct
where TRANSLATE( 
LEFT( LTRIM(hos.hpid), 3 ),
'AAAAAAAAAAAAAAAAAAAAAAAAA', 
'BCDEFGHIJKLMNOPQRSTUVWXYZ'
 ) <> 'AAA'
order by pat.f1; 

请注意，在WHERE子句中使用这些函数意味着性能将受到影响。至少，查询引擎将必须进行全索引扫描;它可以进行全表扫描。

Note that the use of such functions in the WHERE clause means that performance is going to take a hit. At minimum, the query engine will have to do a full index scan; it may do a full table scan.

如果这是一次性事物或小表，这不是一个大问题。但是如果你需要经常在一个大表上查看你的平台和版本的DB2是否支持索引中的表达式...

If this is a one time thing or a small table, it's not a big deal. But if you need to do this often on a big table look to see if your platform and version of DB2 supports expressions in indexes...

create index myindex on hospidl1 
  ( TRANSLATE( 
LEFT( TRIM(hpid), 3 ),
'AAAAAAAAAAAAAAAAAAAAAAAAA', 
'BCDEFGHIJKLMNOPQRSTUVWXYZ'
 ) );

这篇关于在DB2中修剪Char列中的空白空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！