locked
Sql Join on Int part of Column RRS feed

  • Question

  • Declare @TableA Table (Id nvarchar(50))
    Insert Into @TableA
    select 'As123x' Union all
    select '456s&x' Union all
    select 'dsssd789' Union all
    select 'As_888x'

    Select * from @TableA

    Declare @TableB Table(ID int,City nvarchar(100))
     Insert Into @TableB
    Select 123,'London' Union all
    Select 456,'Mumbai' Union all
    Select 999,'Delhi' Union all
    Select 088,'Jaipur' Union all
    Select 099,'London'


    Select * from @TableB

    Output Required: I need the City from Table  B where Id of TableB Matches with
    Integer  part of TableA

    Any Help?


    • Edited by MS308 Friday, April 13, 2012 10:24 AM
    Friday, April 13, 2012 10:23 AM

Answers

  • Here is one solution:

    Declare @TableA Table (Id nvarchar(50))
     Insert Into @TableA
     select 'As123x' Union all
     select '456s&x' Union all
     select 'dsssd789' Union all
     select 'dss9999' Union all
     select 'As_888x'
     
     
    Declare @TableB Table(ID int,City nvarchar(100))
      Insert Into @TableB
     Select 123,'London' Union all
     Select 456,'Mumbai' Union all
     Select 999,'Delhi' Union all
     Select 9999,'Berlin' Union all
     Select 088,'Jaipur' Union all
     Select 099,'London' 
    
    
    Select *
    from @TableB B
    inner join @TableA A
    ON PATINDEX('%' + CAST(B.ID AS VARCHAR(32)) + '%', A.ID) > 0
    AND ASCII(COALESCE(NULLIF(SUBSTRING(A.ID,PATINDEX('%' + 
        CAST(B.ID AS VARCHAR(32)) + '%', A.ID)
        +LEN(CAST(B.ID AS VARCHAR(32))),1),''),'A')) NOT BETWEEN 48 AND 57 
    /* 
    ID	City	Id
    123	London	As123x
    456	Mumbai	456s&x
    9999	Berlin	dss9999  */


    JOIN article:

    http://www.sqlusa.com/bestpractices2005/innerjoin/


    Kalman Toth SQL SERVER & BI TRAINING



    • Marked as answer by MS308 Friday, April 13, 2012 1:14 PM
    • Edited by Kalman Toth Friday, April 13, 2012 2:16 PM
    Friday, April 13, 2012 10:36 AM

All replies

  • Use PATINDEX, e.g.

    SELECT	*
    FROM	@TableA A
    		INNER JOIN @TableB B ON PATINDEX('%' + CAST(B.ID AS VARCHAR(255)) + '%', A.Id) > 0

    or LIKE, e.g.

    SELECT	*
    FROM	@TableA A
    		INNER JOIN @TableB B ON A.Id LIKE '%' + CAST(B.ID AS VARCHAR(255)) + '%'

    • Edited by Stefan Hoffmann Friday, April 13, 2012 10:37 AM
    • Proposed as answer by Naomi N Friday, April 13, 2012 12:38 PM
    • Unproposed as answer by Naomi N Friday, April 13, 2012 12:39 PM
    Friday, April 13, 2012 10:35 AM
  • Here is one solution:

    Declare @TableA Table (Id nvarchar(50))
     Insert Into @TableA
     select 'As123x' Union all
     select '456s&x' Union all
     select 'dsssd789' Union all
     select 'dss9999' Union all
     select 'As_888x'
     
     
    Declare @TableB Table(ID int,City nvarchar(100))
      Insert Into @TableB
     Select 123,'London' Union all
     Select 456,'Mumbai' Union all
     Select 999,'Delhi' Union all
     Select 9999,'Berlin' Union all
     Select 088,'Jaipur' Union all
     Select 099,'London' 
    
    
    Select *
    from @TableB B
    inner join @TableA A
    ON PATINDEX('%' + CAST(B.ID AS VARCHAR(32)) + '%', A.ID) > 0
    AND ASCII(COALESCE(NULLIF(SUBSTRING(A.ID,PATINDEX('%' + 
        CAST(B.ID AS VARCHAR(32)) + '%', A.ID)
        +LEN(CAST(B.ID AS VARCHAR(32))),1),''),'A')) NOT BETWEEN 48 AND 57 
    /* 
    ID	City	Id
    123	London	As123x
    456	Mumbai	456s&x
    9999	Berlin	dss9999  */


    JOIN article:

    http://www.sqlusa.com/bestpractices2005/innerjoin/


    Kalman Toth SQL SERVER & BI TRAINING



    • Marked as answer by MS308 Friday, April 13, 2012 1:14 PM
    • Edited by Kalman Toth Friday, April 13, 2012 2:16 PM
    Friday, April 13, 2012 10:36 AM
  • select a.* from @TableA a inner join @TableB b on a.ID like '%'+cast(b.Id as varchar(10)) +'%'

    OR

    select a.* from @TableA a ,@TableB b
    where len(a.id)-len(REPLACE(a.Id,b.ID,''))>0


    Thanks and regards, Rishabh , Microsoft Community Contributor


    • Edited by Rishabh K Friday, April 13, 2012 10:39 AM
    Friday, April 13, 2012 10:37 AM
  • SELECT * FROM @TableA A
    INNER JOIN
    @TableB B ON A.Id LIKE '%' + CAST(B.ID AS VARCHAR(255)) + '%'

    Easy.

    Vinu Vijayan

    Friday, April 13, 2012 10:41 AM
  • Additionally If you need the leading 0's to be preserved while looking for a match , then you have to declare your ID column in TableB as a character column instead of int

    Thanks and regards, Rishabh , Microsoft Community Contributor

    Friday, April 13, 2012 10:48 AM
  • Match criteria are a bit uncertain.

    Should '456s&x'  match integer 56? integer 5? I guess no. So the solution shoudn't search string-casted integer in the '456s&x' but rather extract integer 456 from '456s&x'  and compare it to the integer field.


    Serg

    • Proposed as answer by Naomi N Friday, April 13, 2012 12:39 PM
    • Unproposed as answer by Kalman Toth Friday, April 13, 2012 12:51 PM
    • Proposed as answer by Naomi N Friday, April 13, 2012 12:52 PM
    • Marked as answer by MS308 Friday, April 13, 2012 1:17 PM
    • Unmarked as answer by MS308 Friday, April 13, 2012 1:26 PM
    Friday, April 13, 2012 11:51 AM
  • I agree with SergNL, and I would extract the integer or numeric literal part into a persisted computed column, to be able to add an index to support the join.


    AMB

    Some guidelines for posting questions...

    • Edited by HunchbackMVP Friday, April 13, 2012 12:34 PM
    • Proposed as answer by Naomi N Friday, April 13, 2012 12:39 PM
    • Unproposed as answer by Kalman Toth Friday, April 13, 2012 12:51 PM
    Friday, April 13, 2012 12:33 PM
  • This solution means introducing a constraint derived from a few lines of sample data. What about 'From088To456' as value? But I agree with general problem of string vs. integer match:

    DECLARE	@TableA TABLE ( Id NVARCHAR(50) );
    
    INSERT	INTO @TableA
    VALUES	( 'Delhi999' );
    
    DECLARE	@TableB TABLE
    	(
    	  ID INT ,
    	  City NVARCHAR(100)
    	);
    
    INSERT	INTO @TableB
    VALUES	( 999, 'Delhi' ),
    		( 099, 'London' );
    
    SELECT	*
    FROM	@TableA A
    		INNER JOIN @TableB B ON A.Id LIKE '%' + CAST(B.ID AS VARCHAR(255)) + '%';

    Friday, April 13, 2012 12:43 PM
  • For extracting numbers from a string you may want to check this blog post

    Extracting numbers with SQL Server


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, April 13, 2012 12:53 PM
  • Hi SQLUSA,

    Thanks ,I am getting Expected Output with your Query :)
    If U could explain me the last Condition Why did we used

    AND ASCII(SUBSTRING(A.ID,PATINDEX('%' + CAST(B.ID AS VARCHAR(32)) + '%', A.ID)
        +LEN(CAST(B.ID AS VARCHAR(32))),1)) NOT BETWEEN 48 AND 57

    and what does above expression actually do? This is just for my Knowledge .
    If u dont mind Explaining!!


    • Edited by MS308 Friday, April 13, 2012 1:28 PM
    Friday, April 13, 2012 1:26 PM
  • That is to prevent 88 integer ID to match x888x string imbedded ID. It checks if the match following character (888) is a digit. If digit, it considers it no match.

    Kalman Toth SQL SERVER & BI TRAINING


    Friday, April 13, 2012 1:53 PM