• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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.
0
patd1
Asked:
patd1
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now