x

Space issue in data

i am joining two tables based on column comm. but in both the tables there is a data problem with spaces like "010 - aaaa > 9'" and in another table "010 - aaaa > 9'". How to get rid of extra space after 010 while joining.

more ▼

asked Aug 27, 2012 at 04:09 PM in Default

Mohan gravatar image

Mohan
324 37 56 60

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can specify more than just a column name on either side of a JOIN clause, eg:

SELECT * 
FROM foo 
LEFT JOIN bar ON foo.SomeString = REPLACE(bar.SomeString, ' ', '')
However, performance will not be so good.
more ▼

answered Aug 27, 2012 at 04:39 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

Replacing all the spaces from only one side may not yield the correct results. So my preference would be replacing all the spaces on both sides and also do a FULL OUTER JOIN to see all the non-matching values. But +1 for the core idea though :)
Aug 28, 2012 at 01:53 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986

asked: Aug 27, 2012 at 04:09 PM

Seen: 419 times

Last Updated: Aug 28, 2012 at 01:53 PM