none
Join

    Question

  • CREATE TABLE #Temp ( PN varchar(10), name varchar(30) )
    INSERT INTO #Temp
            ( PN , name )
    VALUES
            ( '1' , 'A & J FRIEDMAN'),
              ('2','BAYLINER MARINE CORP.')
              
              --DROP TABLE #Temp
    CREATE TABLE #temp2 (NAME VARCHAR(30))
    INSERT INTO #temp2 ( NAME )
    VALUES
                      ( 'FRIEDMAN, A & J '),
                        ('BAYLINER')
                        
                        SELECT * FROM  #Temp AS t
                        SELECT * FROM  #temp2 AS t2                                                                                                                                i want to join on Column 'NAME' but as you can see in 1st record both are same but its opposite and and 2nd record starting char are match, i have thousands of records like that, can you guys tell me easy way to identify/ join easily thanks  

    Thursday, June 27, 2013 3:13 PM

Answers

  • Thanks you for the DDL, but you did it wrong. There are no keys, the column names violate ISO-11179 rules and there are no constraints. 

    Since 80-95% of the work in SQL is done in the DDL this is important. 

    CREATE TABLE Suppliers
    (supplier_duns CHAR(9) NOT NULL PRIMARY KEY,
     supllier_name VARCHAR(30) NOT NULL);

    INSERT INTO Suppliers
    VALUES
    ('111111111', 'A & J Friedman'),
    ('222222222', 'Bayliner Marine Corp.');

    CREATE TABLE Redundant_Suppliers 
    (supplier_name VARCHAR(30) NOT NULL PRIMARY KEY);

    INSERT INTO Redundant_Suppliers (supplier_name)
    VALUES ('Friedman, A & J '),
     ('Bayliner');
     
    >> I want to join on Column 'supplier_name' but as you can see in 1st record [sic] both are same but its opposite and and 2nd record [sic] starting characters are match, I have thousands of records [sic] like that, can you guys tell me easy way to identify/ join easily <<

    Rows are not records; the differences are important. The short answer is “No”; SQL was not meant to be a string language. A better answer is to use a mailing list scrubbing package to clean up your data. I like Melissa Data, but there are other packages. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, June 27, 2013 7:29 PM

All replies

  • One of the possibility is to is to create another table with unique values similar to names from both these tables and join them together.

    Here is a simple example (based on your code)

    DECLARE @Temp TABLE ( PN varchar(10), name varchar(30) )
    DECLARE @temp2 Table (NAME VARCHAR(30))
    
    INSERT INTO @Temp ( PN , name ) VALUES ( '1' , 'A & J FRIEDMAN'), ('2','BAYLINER MARINE CORP.')
    INSERT INTO @temp2 ( NAME ) VALUES ( 'FRIEDMAN, A & J '),('BAYLINER')
    
    DECLARE @UniqueNames TABLE ( Name varchar(30) )
    INSERT INTO @UniqueNames VALUES ('FRIEDMAN'),('BAYLINER')
    	                
    SELECT *
      FROM @UniqueNames U
          left outer join @Temp T1 on T1.name like '%'+u.Name+'%'
          left outer join @temp2 T2 on T2.NAME like '%'+u.Name+'%'
    

    You can play with LIKE operator and wildcard characters to get maybe better results.


    Thursday, June 27, 2013 6:04 PM
  • Hello Sam,

    You have trashy data; there is no way to easily match them. You have to clean up your data in a more manually process.


    Olaf Helper

    Blog Xing

    Thursday, June 27, 2013 6:08 PM
  • Yup OLAF, thats what i am doing right now... thanks
    Thursday, June 27, 2013 6:25 PM
  • Not easy in SQL. Can you use SSIS with Fuzzy lookup? Also there is an Excel plugin from Microsoft to do fuzzy lookups; but, I have not tried it. Excel plugin here http://www.microsoft.com/en-us/download/details.aspx?id=15011
    Thursday, June 27, 2013 6:48 PM
  • Thanks you for the DDL, but you did it wrong. There are no keys, the column names violate ISO-11179 rules and there are no constraints. 

    Since 80-95% of the work in SQL is done in the DDL this is important. 

    CREATE TABLE Suppliers
    (supplier_duns CHAR(9) NOT NULL PRIMARY KEY,
     supllier_name VARCHAR(30) NOT NULL);

    INSERT INTO Suppliers
    VALUES
    ('111111111', 'A & J Friedman'),
    ('222222222', 'Bayliner Marine Corp.');

    CREATE TABLE Redundant_Suppliers 
    (supplier_name VARCHAR(30) NOT NULL PRIMARY KEY);

    INSERT INTO Redundant_Suppliers (supplier_name)
    VALUES ('Friedman, A & J '),
     ('Bayliner');
     
    >> I want to join on Column 'supplier_name' but as you can see in 1st record [sic] both are same but its opposite and and 2nd record [sic] starting characters are match, I have thousands of records [sic] like that, can you guys tell me easy way to identify/ join easily <<

    Rows are not records; the differences are important. The short answer is “No”; SQL was not meant to be a string language. A better answer is to use a mailing list scrubbing package to clean up your data. I like Melissa Data, but there are other packages. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, June 27, 2013 7:29 PM