Solved

not able to get rid of trailing space in my column

Posted on 2012-03-12
3
262 Views
Last Modified: 2012-06-27
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
Comment
Question by:patd1
3 Comments
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37712397
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
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 37712416
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
 

Author Closing Comment

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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question