Substring?

Transact-SQL
1

Hi all, i need some help regarding SQL.

i have a column in a table (let's say A.) which contains data like this:

MATTHIEU FOURNIER
Computacenter AG & Co oHG
KPMG
WA DET
LENOVO
WOOLWORTHS LIMITED
LENOVO C/O DHL Supply Chain
Deutsche Telekom / T-Systems

what i need is to be able to identify names from those lines, for example Fournier only and look for that name in another table( B), if there is a match then select the match from the table B and fill another column from table A.

it's something like the fuzzylookup from excel.

problem: i do not know when the name is going to appear, i do not know the lenght nor the start. it could be in the middle, start or end.

do you know if this is going to have any kind of solution? i've tried with the substring, however it's not solving my problem.

thanks in advance!

2

check out the PATINDEX funcion:

https://docs.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql

you can use this, e.g. if the text you want is delimited by blanks

3

See if this is giving the desired results...

IF OBJECT_ID('tempdb..#WordsTable', 'U') IS NOT NULL 
DROP TABLE #WordsTable;

CREATE TABLE #WordsTable (
	Word VARCHAR(20) NOT NULL PRIMARY KEY
	);
INSERT #WordsTable (Word) VALUES
	('FOURNIER'),
	('WOOLWORTHS'),
	('DHL'),
	('LENOVO');

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	TextString VARCHAR(100) NOT NULL 
	);
INSERT #TestData (TextString) VALUES
	('MATTHIEU FOURNIER'),
	('Computacenter AG & Co oHG'),
	('KPMG'),
	('WA DET'),
	('LENOVO'),
	('WOOLWORTHS LIMITED'),
	('LENOVO C/O DHL Supply Chain'),
	('Deutsche Telekom / T-Systems');

--======================================

SELECT 
	td.TextString,
	MatchedWords = STUFF(wx.MatchedWords, 1, 2, '')
FROM 
	#TestData td
	OUTER APPLY (
			SELECT (
				SELECT 
					CONCAT(', ', wt.Word)
				FROM 
					#WordsTable wt
				WHERE 
					td.TextString LIKE '%' + wt.Word + '%'
				ORDER BY 
					wt.Word
				FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
			) wx (MatchedWords);