Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    21

    Unanswered: Ignore character in JOIN

    Hi,

    I have 2 tables in which there are 2 fields which hold the same piece of data. In one of the fields however there is a "_" character within the value.

    So when comparing the 2 directly they will not be the same.

    So the example is the following

    Table 1, field 1 value is 1000220_1

    Table 2, field 2 value is 10002201

    So in table 1 the "_" is always in the 8th position.

    Does anybody know a way in the JOIN statement how I can maybe ignore the 8th character in field 1 ? Or ignore any "_" it finds?

    Any help is appreciated!

    Thanks,
    Michiel

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT
       Stuff('1000220_1', 8, 1, '')		-- Drop the eighth character
    ,  Replace('1000220_1', '_', '')	-- Drop any underscores
    -PatP

  3. #3
    Join Date
    Jan 2008
    Posts
    21
    Excellent. Thanks Pat!

Posting Permissions

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