CODEWITHSUNDEEP
sqlsql-server
Chester Lim
Chester Lim

Reputation: 499

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.

Upvotes: 1

Views: 6814

Answers (4)

Sturgus
Sturgus

Reputation: 686

This doesn't answer the original question, but it may function as a work-around:

INNER JOIN view AS vw ON CONVERT(integer, vw.[Id]) = CONVERT(integer, vw2.[Id])

If all of the IDs are integer-like (as your '2012 ' example is).

Upvotes: 1

Forrest
Forrest

Reputation: 133

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.

Upvotes: 2

Sturgus
Sturgus

Reputation: 686

Your logic seems right. Have you tried:

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

?

You could also combine trims and replaces as a way to get rid of all of the whitespace. Though, it seems like using a sledgehammer to get what you want...

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

Upvotes: 1

Safford96
Safford96

Reputation: 23

Try this:

INNER JOIN view AS vw ON ltrim(rtrim(vw.[Id])) = ltrim(rtrim(vw2.[Id]))

Upvotes: 1

Related Questions