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

avatar image

Mohan
324 49 59 63

(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

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

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.

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:

x1069

asked: Aug 27, 2012 at 04:09 PM

Seen: 473 times

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

Copyright 2016 Redgate Software. Privacy Policy