not able to get rid of trailing space in my column

I want to join two tables on ImageFilename  column. the name is the same in both the tables, except for a trailing space in Table B. S, I am using Replace function to get rid of the space, but still can't get rid of the space. What 's wrong with my join?

Select A.ImageFileName, B.ImageFileName
From [MyDB].[dbo].[TableA] A
join  [MyDB].[dbo].[TableB] B on Replace(A.ImageFileName, ' ', '') = Replace(B.ImageFileName, ' ', '')

Sample data: (I added a * at both the ends to give you an idea of what's happening.
Table A:
*H3708_079019200_731481229001_UNIH3708HD12.pdf*
*H3708_079020195_731420444101_UNIH3708HD12.pdf*
*H3708_079020206_731481229001_UNIH3708HD12.pdf*
*H3708_079020210_731461537101_UNIH3708HD12.pdf*

Table B:
*H3708_079019200_731481229001_UNIH3708HD12.pdf *
*H3708_079020195_731420444101_UNIH3708HD12.pdf *
*H3708_079020206_731481229001_UNIH3708HD12.pdf *
*H3708_079020210_731461537101_UNIH3708HD12.pdf *

if I manually edit Table B to remove trailing space, it joins.

How should I proceed without the mannual edit?

Thanks.
patd1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick TallaricoFSEP Systems AnalystCommented:
I may make sense to have the spaces removed as the table is populated.  This way there is less work to be done for the join when the query is run.
I am assuming there is a process to read filenames from a server and input this into the table on the sql server.

Just curious, if you re-arrange your query to pull from subqueries where the Replace function is performed before the join, does it work?
It should look something like this...

Select A.AFileName, B.BFileName
From (Select Replace(ImageFileName, ' ', '') as [AFileName] from [MyDB].[dbo].[TableA]) as A
join  (Select Replace(ImageFileName, ' ', '') as [BFileName] from [MyDB].[dbo].[TableB]) as B on A.AFileName = B.BFileName
0
Martin LissOlder than dirtCommented:
It's probably not a space. Could it be a carriage return? You can use something like


MsgBox Asc(Right(A.ImageFileName, 1))

to find out what it is and then use Replace with Asc(nn) instead of the " ", where 'nn' is the ascii value.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patd1Author Commented:
It was a tab, not a space.
This finally works by joining on
 Replace(A.ImageFileName, CHAR(9), '') = Replace(B.ImageFileName, CHAR(9), '')
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.