Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Criteria searches

    I am attempting to compare two databases (with over 11000 records in each)
    In one table I have a "Sage" generated account reference which uses the first 4 characters of the surname followed by a number if more than one of the same name, the second table has the full surname followed by the chritian name.

    I am trying to create an SQL statement (using wildcards) to SELECT the first 4 letters from table 1 and compare them with the first 4 letters from table 2. To then display lists of matching names.

    Ideally I would like to be able to SELECT any length of sequence of characters which match any length of the names from any area of the field.

    eg Table 1 Account reference = qweABBOTqwe32
    Table 2 Name = ABBOT JIMMY

    Need to 'Pick out' ABBOT from the field in table 1 to match ABBOT from the field in table 2

    PLEASE PLEASE help as I am a beginner at this and it is becoming very frustrating

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    This will show all where the first 4 characters of the name match.

    select table1.name as t1Name, table2.name as t2Name
    from table1 inner join table2 on left(table1.name,4) = left(table2.name,4);

    which gives you:
    t1Name t2Name
    ABBO01 Abbot
    ABBO01 Abbotty

    I have no idea what your qwe is.

    another option that may work:

    select table1.name as t1Name, table2.name as t2Name
    from table1, table2
    where table2.name like left(table1.name,4) & "*";

    which gives the same data as the previous

  3. #3
    Join Date
    Feb 2004
    Posts
    2

    Many thanks

    All I can say is endless thanks for this help. I was able to sort all the records and compare one DB with 11000 records against another with over 40000. Again thanks.


    Originally posted by jmrSudbury
    This will show all where the first 4 characters of the name match.

    select table1.name as t1Name, table2.name as t2Name
    from table1 inner join table2 on left(table1.name,4) = left(table2.name,4);

    which gives you:
    t1Name t2Name
    ABBO01 Abbot
    ABBO01 Abbotty

    I have no idea what your qwe is.

    another option that may work:

    select table1.name as t1Name, table2.name as t2Name
    from table1, table2
    where table2.name like left(table1.name,4) & "*";

    which gives the same data as the previous

Posting Permissions

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