Deleting Spaces!

I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:

Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348

I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.

All help would be appreciated.Try this query on your table:

UPDATE [Table 1] SET Loc_Code = Replace([Loc_Code], (Chr(32)), "");

This should get rid of that space.|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)|||You can also use trim -

select a1.col1, a2.col1
from test a, test1 a2
where a1.col3 = trim(a2.col3)
!!Cough!!Bullsht!!Cough!!|||aw, c'mon, nocopy, be nice, show the poor guy the right way

since this is the SQL forum, for the SQL language and not any specific implementation thereof, i shall give an SQL solution

estefex, here's your join --
select Table1.foo
, Table2.bar
from Table1
inner
join Table2
on substring(Table1.Loc_Code from 1 for 1)
|| substring(Table1.Loc_Code from 3 for 5)
= Table2.Loc_Code|||Mmm, sorry r937 I'll tone it down. ;)

dj982020 already gave a solution, the replace thing it is.

By the way, your code won't run on my DB. The replace will though.

ss659 No hard feelings mmmkay? ;)|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:|||r937, your code is not robust also.
The replace will plow through as many spaces as you throw at it.
Well I would use it in a join instead of updatin' cause maube the other table wants it this way.

I feel mighty feisty today.|||dj982020's solution will work only in microsoft databases

and if your database does not run standard sql, i suggest you get a better database

:cool: :cool: :cool:
You being bad too.

trim takes one character only 'tsup with dat?
rtrim ltrim kicks bigger A$$.|||maybe estefex's database does not have the replace or trim functions, did you ever consider that?

do you even know what database estefex is running?

no

therefore standard sql is the best solution

and trim will not remove a space from inside a value|||and trim will not remove a space from inside a value

Cough!!True!!Cough!!

Now I KNOW you know standard SQL better than me.
Is this the best standard SQL can do then? :eek:|||well, i don't really want to get into a discussion of whether standard sql is any good or not, or "the best it can do"

all i wanted to do was point out that in this forum, standard sql should be used

especially if the poster does not indicate which database system they're using

i mean, if somebody wanted an oracle solution, there's a forum for oracle

if somebody wanted an access solution, there's a forum for access

if somebody wanted an sql server solution, there's a forum for sql server

if somebody wanted a mysql solution, there's a forum for mysql

what do you think this forum is for?|||R937
Chill, chill. You right. :cool:

Maybe folks ought to mention what DB or DBs they are running.
Then there would be no confusion.
Who needs to read the crystal reports err.. bowl, right? ;)|||!!Cough!!Bullsht!!Cough!!

Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!|||Funny - it says you have a WHOPPING 23 posts, 6 of which are on this page. Ive noticed you have not actually given any of your OWN ideas, just sat back and critiqued every one elses :) http://www.dbforums.com/showpost.php?p=3671341&postcount=9 One of his better STELLAR posts again..bringing so much to the table. Im sure you have an important job out in the community though so you're too busy to formulate your own thoughts.

And to be honest I never looked at the data for the original post - I just read " I want to erase spaces in one table to join to another" - Trimming a column will take care of the leading and trailing spaces, and yes you're right won't trim within a column. But as we ALL know, most of the data people post is not what actually resides in the actual database. Im sure he really has a table called table1 and table2...riiight..you idiot!
Hah, if you got a high post count you think you are the shit? :D
Maybe you ought to start reading the question before you respond.
Now, I am not going to plow through the millions of your posts and see if they are of the similar quality as the one here. And unlike you I am not going to call you names.

By the way, did you ask r937 if he was offended by my posts? I think not.
Have a nice life.
Apologies for the Idiot will be accepted. :rolleyes: