We help IT Professionals succeed at work.

not able to get rid of trailing space in my column

patd1 asked
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:

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?

Watch Question

Patrick TallaricoDecision Support and Systems Analyst

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
Social distance - Don't touch your face - Wash your hands for 20 seconds
Most Valuable Expert 2017
Distinguished Expert 2018
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.


It was a tab, not a space.
This finally works by joining on
 Replace(A.ImageFileName, CHAR(9), '') = Replace(B.ImageFileName, CHAR(9), '')

Explore More ContentExplore courses, solutions, and other research materials related to this topic.