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

Question

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

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 · Answer 2

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 3

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.