Complex string condition: How to get table records where 3 consecutive letters matched in string column

• anujkumar.mca

  SSChasing Mays

  Points: 621

  More actions

  January 8, 2018 at 11:15 pm

  #409936

  Hello,

  I have 2 Tables both have a column (App_name) which contains  application name. I need search the records where minimum 3 consecutive letters are matched.
  Example:Table A has complete name, but table B has random user input names. I need to find out where at least 3 letter of table B matched with Tables A. Any 3 consecutive letter of Table A should be match with any 3 consecutive letter of Table B (it could be anywhere in string).
  Tried so many permutation combination but didn't get any luck.
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  January 8, 2018 at 11:40 pm

  #1975022

  anujkumar.mca - Monday, January 8, 2018 11:15 PM

  Hello,

  I have 2 Tables both have a column (App_name) which contains  application name. I need search the records where minimum 3 consecutive letters are matched.
  Example:Table A has complete name, but table B has random user input names. I need to find out where at least 3 letter of table B matched with Tables A. Any 3 consecutive letter of Table A should be match with any 3 consecutive letter of Table B (it could be anywhere in string).
  Tried so many permutation combination but didn't get any luck.
  

  First of all, do post sample schema and data as a consumable script, makes it a lot easier to answer.
  😎
  There are several ways of doing this, here is one example:
  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @TABLE_A TABLE
(
  TA_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
 ,TA_NAME VARCHAR(100)  NOT NULL
);
DECLARE @TABLE_B TABLE
(
  TB_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
 ,TB_NAME VARCHAR(100)  NOT NULL
);

  INSERT INTO @TABLE_A(TA_NAME)
VALUES ('FULL APP NAME WITH AB STRING')
  ,('FULL APP NAME WITH DEF STRING')
  ,('FULL APP NAME WITH GI STRING')
  ,('FULL APP NAME WITH JKL STRING')
  ,('FULL APP NAME WITH MNO STRING')
  ,('FULL APP NAME WITH NOP STRING')
;
INSERT INTO @TABLE_B(TB_NAME)
VALUES ('ABC')
  ,('DEF')
  ,('GHI')
  ,('JKL')
  ,('MNOP')
;

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) X(N))
,SEARCH_BASE AS
(
  SELECT
   TB.TB_ID
   ,TB.TB_NAME
   ,CONCAT(CHAR(37),SUBSTRING(TB.TB_NAME,T1.N,3),CHAR(37)) AS STRPRT
  FROM  @TABLE_B  TB
  CROSS APPLY T   T1
  WHERE T1.N  <= (LEN(TB.TB_NAME) - 2)
)
SELECT
  TA.TA_ID
 ,TA.TA_NAME
 ,SB.TB_ID
 ,SB.TB_NAME
FROM   @TABLE_A   TA
CROSS APPLY SEARCH_BASE  SB 
WHERE   TA.TA_NAME LIKE SB.STRPRT
;


  Output
  
TA_ID    TA_NAME    TB_ID    TB_NAME
2    FULL APP NAME WITH DEF STRING    2    DEF
4    FULL APP NAME WITH JKL STRING    4    JKL
5    FULL APP NAME WITH MNO STRING    5    MNOP
6    FULL APP NAME WITH NOP STRING    5    MNOP

  

• anujkumar.mca

  SSChasing Mays

  Points: 621

  More actions

  January 9, 2018 at 1:04 am

  #1975028

  Thank you so much, Perhaps I was not able to think at this level.

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  January 9, 2018 at 1:28 am

  #1975032

  anujkumar.mca - Tuesday, January 9, 2018 1:04 AM

  Thank you so much, Perhaps I was not able to think at this level.

  You are welcome.
  😎

  The code is an example, not a fully fledged solution, study it carefully before implementing it, ping back if you have any questions.