Chester Lim Chester Lim - 7 months ago 24
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?

EDIT

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

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)
)

INSERT INTO #table1
VALUES
('2012')
,('2013')
,('2014')
,('2015')
,('2016')

INSERT INTO #table2
VALUES
('2012'+CHAR(9))
,('2013'+CHAR(9))
,('2014'+CHAR(9))
,('2015'+CHAR(9))
,('2016'+CHAR(9))

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.