?
Solved

not able to get rid of trailing space in my column

Posted on 2012-03-12
3
Medium Priority
?
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

Accepted Solution

by:
Martin Liss earned 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 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