Chester Lim Chester Lim - 2 years ago 103
SQL Question

Removing tab spaces in sql server 2012

I have this query

INNER JOIN view AS vw ON vw.[Id] = vw2.[Id]

The problem is the return in vw2.[Id] contains a tab space at the end ('2012 ') and vw does not ('2012'). So i tried doing

INNER JOIN view AS vw ON vw.[Id] = Replace(vw2.[Id], char(9), '')

Unfortunately, the comparison still returns false. I thought that it cannot be done on Joins so i tried it on a select query

SELECT * FROM database.view2 WHERE REPLACE(Id, char(9), '') = '2012 '

But this query also returns a false. Can someone explain to me what concept am i missing or misunderstanding?


Hello and thank you to everyone who took their time to help me on this. It seems that vw.[Id] = LTRIM(RTRIM(REPLACE(vw2.[Id], char(9), '') did the trick. I keep on using REPLACE on both ends which resulted from a very long query time which is not necessary. I used LTRIM and RTRIM also to get the data but from the return of varbinary it should not be needed and i don't understand what i'm doing wrong. But it works now thank you everyone.

Answer Source

I suspect there may be more characters you're dealing with than just a tab. For example, you include REPLACE(Id, char(9), '') = '2012 ' Why is there still a space on the end after the replace?

I was able to get your method to work in SQL 2008R2, so below is proof-of-concept code.

CREATE TABLE #table1 (
Id varchar(5)

CREATE TABLE #table2 (
Id varchar(5)



SELECT t1.Id, t2.Id
FROM #table1 t1
INNER JOIN #table2 t2
    ON t1.Id = REPLACE(t2.Id,CHAR(9),'')

See if that gives you the proper results - it does for me.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download