Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2004
    Posts
    2
    Try this query on your table:

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

    This should get rid of that space.

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    You can also use trim -

    select a1.col1, a2.col1
    from test a, test1 a2
    where a1.col3 = trim(a2.col3)

  4. #4
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by ss659
    You can also use trim -

    select a1.col1, a2.col1
    from test a, test1 a2
    where a1.col3 = trim(a2.col3)
    !!Cough!!Bullsht!!Cough!!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    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?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    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.

  9. #9
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by r937
    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

    You being bad too.

    trim takes one character only 'tsup with dat?
    rtrim ltrim kicks bigger A$$.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by r937
    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?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    R937
    Chill, chill. You right.

    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?
    Last edited by Nocopy; 05-02-04 at 22:30.

  14. #14
    Join Date
    Jan 2004
    Posts
    492
    Quote Originally Posted by Nocopy
    !!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...41&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!
    Last edited by ss659; 05-03-04 at 09:00.

  15. #15
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by ss659
    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...41&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?
    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.
    My way or the highway. Yeah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •