CODEWITHSUNDEEP
sqlstringoracle-databasejoin
Aaron
Aaron

Reputation: 331

Use TRIM in JOIN

I have the following SQL code

SELECT      State.CODE                    Code, 
            State.CODE1                   Code1, 
            State.CODE2                   Code2, 
            YYY.1003."BRANCH"            "Branch" 
     FROM   XXX.1002 State
            LEFT JOIN YYY.1003 
                   ON State.CODE= YYY.1003.CODE

Now I need a new column that shows me the field/ column "Country" from table ZZZ.100. The key is 1002.CODE1 - ZZZ.100.CODE1

Unfortunately, ZZZ.100.CODE1 has spaces before the values (4 spaces).

How can I use the trim function (is this the right one) to get a join on 1002.CODE1 - ZZZ.100.CODE1

Upvotes: 1

Views: 11372

Answers (2)

Julian Leite
Julian Leite

Reputation: 112

First thing is why a Key column contains spaces on it? A key column should never contain spaces, it is terrible for performance. The point to think about here is to remove those spaces from this column

The use of TRIM, considering you are using SQL Server In SQL Server you can use LTRIM (for left trim, remove left spaces) and/or RTRIM (for right trim, remove right spaces)

Ex: Select LTRIM(RTRIM(Table.Column)) from Table

I hope this can help you

Upvotes: 1

GMB
GMB

Reputation: 222502

It looks like you want:

select x.code, x.code1, x.code2, y.branch, z.country
from xxx.1002 x
left join yyy.1003 y on x.code= y.code
left join zzz.100  z on trim(z.code1) = x.code1

trim() removes spaces on both ends of the string. If you want to remove only leading spaces, you can do: trim(leading ' ' from z.code1).

Note that I used more meaningful table aliases, in order to make the query easier to write and read.

I would also reommend against using all-digits table names: in Oracle, non-quoted identifiers must begin begin with an alphabetic character.

Upvotes: 1

Related Questions