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 50 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 ♦♦
42k 20 51 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:

x1089

asked: Aug 27, 2012 at 04:09 PM

Seen: 473 times

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

Copyright 2017 Redgate Software. Privacy Policy