Compare Two Strings For Common Value

Question

Is it possible to compare two strings and find a common value between the two? For example, 'Alpha Bravo' and 'Echo Charlie Bravo Delta' have a common word which is 'Bravo'.

In my scenario, I have two tables sharing the similar formatted words (i.e. Alpha Bravo Charlie Delta Echo) in a column and I would like to find whether a similarity in word(s) exist so a condition is met.

Thanks for any direction.

The commas don't match. – Gordon Linoff Mar 13 '20 at 17:16 — Gordon Linoff, Mar 13 '20 at 17:16

Barbaros Özhan · Accepted Answer · 2020-03-13T19:51:41.797

1

If your DB version is 2016+, then you can create queries containing STRING_SPLIT() function with CROSS APPLY next to each of your tables, and then filter common values through INTERSECT operator :

SELECT value
  FROM tab1  
 CROSS APPLY STRING_SPLIT(str, ' ')
INTERSECT
SELECT value
  FROM tab2 
 CROSS APPLY STRING_SPLIT(str, ' ')

Demo

which yields case-insensitive matching among splitted words.

edited Mar 13 '20 at 19:51

answered Mar 13 '20 at 17:45

Barbaros Özhan

59,113
10
31
55

STRING_SPLIT does not work on sql server, it says invalid object – pty Mar 24 '20 at 20:22
1

Hi @Stefano I showed in the demo that works, but for version 2016, seems you have a former version. – Barbaros Özhan Mar 24 '20 at 20:25
1

for anyone who stumbles on this same problem, check this out: https://stackoverflow.com/questions/47205829/sql-server-2016-invalid-object-name-string-split – pty Mar 24 '20 at 20:50

score 0 · Answer 2 · answered Mar 13 '20 at 17:55

This should work, but I strongly advise against deploying anything like this in production, unless your tables are extremely small. But it's worth posting because it may help you find a proper solution.

SPLIT_STRING returns a one-column table (the column name is [value]), which contains the list of words (see https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15). This column is joined with the first table using LIKE (see the CROSS APPLY block).

-- Declare test tables
DECLARE @TABLE1 table([TEXT] varchar(1000))
DECLARE @TABLE2 table([TEXT] varchar(1000))

-- Populate tables
INSERT INTO @TABLE1
VALUES ('alpha bravo charlie'), ('delta echo lima'), ('golf hotel india'), ('tango uniform victor')

INSERT INTO @TABLE2
VALUES ('alpha lima'), ('tango')

-- Query
SELECT DISTINCT T1.[TEXT]
FROM @TABLE1 T1
INNER JOIN @TABLE2 T2 ON 1 = 1
CROSS APPLY (SELECT * FROM STRING_SPLIT([T2].[TEXT], ' ') T3 WHERE T1.[TEXT] LIKE '%' + T3.value + '%') AS T3

Compare Two Strings For Common Value

2 Answers2