Results 1 to 10 of 10

Thread: Erasing Spaces!

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Erasing 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
    Jul 2003
    Location
    SoCal
    Posts
    721
    Try:

    SELECT * FROM table1 t1, table2 t2 WHERE t2.Loc_Code = SUBSTRING(t1.Loc_Code, 1, 1)+SUBSTRING(t1.Loc_Code, 3, LEN(t1.Loc_Code))

    We're using SUBSTRING to get the first character of t1.Loc_Code, and concatenating the 3rd character through the end of the string to that first character.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Actually.. It's more simple then that...

    SELECT * FROM table1 t1, table2 t2 WHERE t2.sText = REPLACE(t1.sText, ' ', '')

    Just replace the space with nothing.
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    I think SQL also supports a Replace function. Try Replace(string_to_search, string_searched_for, string_to_replace_with).

    So, Replace('A 123456', ' ' ,'') would (should) yield 'A123456'.


    Check BOL.

    Regards,

    hmscott

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are going to run this frequently, consider creating a calculated field in Table1 set to REPLACE(Loc_Code, ' ' , ''), and then index the results.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2004
    Posts
    164
    I see how this would work, But this column contains over 50,000 records. So listing each value would be too long. How would I be able to select all records that have the space and replace it with no space?

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    I think you are looking for something like this:

    Code:
    SELECT
      t1.Text,
      t2.Text
    FROM
      table1 t1 join table2 t2 on
        t1.text = Replace(t2.Text, ' ', '')

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Are you trying to change the records themselves? You could clean the table by doing:

    Code:
    UPDATE table2 SET Text = REPLACE(Text, ' ', '')
    That which does not kill me postpones the inevitable.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If non-numeric prefix is important, move it to a different column.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The best solution is to fix your data.
    If you can't do this, an indexed calculated field is going to be your fastest solution for repeated queries.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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