0

I'm beginning to understand the benefits of not using varchar for keying, as explained here

I am working with a DB2 database which I didn't setup which has a large table (20M+ rows) with a varchar key field. The table structure is as so:

key_field | matching_field1  
000-XXX | 123  
0000-XXX | 456  
00-XXXXX | 789

The key_field is of variable length. I'm trying to use the key_field to link to a smaller table with 4k rows. The problem is that there is trailing space after the characters in the key_field. I know this because if I search for a specific string using

SELECT * WHERE key_field LIKE '000-XXX' there is no match, but if I try '000-XXX%' then the row is selected. I assume this is the same reason I cannot key to my second table which contains a corresponding key_field but which has been trimmed so there are no trailing whitespaces.

Assuming I cannot edit, duplicate or transpose this large table (at least not en masse due to resource limitations), is there a way to key it to the smaller table (4k rows) containing the same key_field?

I can edit and manipulate the smaller table.

Update: running the query below using RTRIM, I'm getting an error.

Query:

SELECT RTRIM(foreign_key)  
FROM Small_Table  
EXCEPT SELECT RTRIM(key_field)  
FROM Big_Table

The error:

NUMBER OF ROWS DISPLAYED IS 0
SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE 00000100, AND
RESOURCE NAME DB2-MANAGED SPACE WITHOUT SECONDARY ALLOCATION OR
USER-MANAGED SPACE IN MYDB_NAME
Community
  • 1
  • 1
joshi123
  • 835
  • 2
  • 13
  • 33
  • Can `TRIM()` be used in a join? – M O'Connell Nov 10 '16 at 01:19
  • 1
    Just attempted it, `TRIM()` can be used in a join in DB2. Efficiency however may suffer. – M O'Connell Nov 10 '16 at 01:25
  • 1
    Not sure what platform you're running DB2 on. On DB2 for Linux, UNIX, Windows, trailing spaces in a `VARCHAR` field are ignored when performing comparisons. (So if the value `ABC ` is stored in a `VARCHAR(10)` column, the predicate `WHERE column = 'ABC'` will still return the row in question. – Ian Bjorhovde Nov 10 '16 at 08:58
  • @IanBjorhovde, it looks like the trailing spaces are being ignored since when I try `WHERE column = 'ABC'` the row is selected. – joshi123 Nov 10 '16 at 11:30

1 Answers1

1

use this commands for join your tables

if you have only spaces to the end of string

rtrim(key_field)=rtrim(keyothertable)

if you have only spaces to the start of string

ltrim(key_field)=ltrim(keyothertable)

if you have spaces to the start or/and end of string (solution 1)

trim(key_field)=trim(keyothertable)

if you have spaces to the start or/and end of string (solution 21)

strip(key_field)=strip(keyothertable)

The drawback when you use "trim/rtrim/ltrim/strip" functions on your keys, is that it is possible that the indexes are not used (to check)

May be can you trasnform your columns key to varchar if indexes are not used...

Esperento57
  • 16,521
  • 3
  • 39
  • 45