CODEWITHSUNDEEP
sqlsql-servert-sql
user1194919
user1194919

Reputation:

Trim whitespace while comparing strings - sql

I have query like 

Select *
From ReportData L 
Left Join ATM A On L.ATMID=A.ATM 
Where L.ATMID=A.ATM AND L.IssuerNetwork < > '0000'

it retrieve result only when ATMID & ATM is like 'TWAW1800 ' & 'TWAW1800 ' But FAILS when 'TWAW1800' & 'TWAW1800 '

What i Tried is 

Select *
From ReportData L 
Left Join ATM A On L.ATMID=A.ATM 
Where LTRIM(RTRIM(L.ATMID))=LTRIM(RTRIM(A.ATM)) AND L.IssuerNetwork < > '0000'

But it doesnt work for me..

Upvotes: 3

Views: 6981

Answers (2)

Jon Marnock
Jon Marnock

Reputation: 3225

Try putting the LTRIM in the ON clause, not the WHERE clause. Where happens after the join, which already throws away the differing matches due to spaces.

As an aside, I think some DBs treat trailing spaces as not actually there. Just FYI.

Upvotes: 3

Taryn
Taryn

Reputation: 247670

Place the LTRIM()/RTRIM() on the JOIN so then it strips the whitespace when joining:

SELECT *
FROM ReportData L 
LEFT JOIN ATM A 
  ON LTRIM(RTRIM(L.ATMID))=LTRIM(RTRIM(A.ATM))
WHERE L.IssuerNetwork <> '0000'

Upvotes: 8

Related Questions