1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
|
WITH data AS (
SELECT 'Panda' AS Label
UNION ALL SELECT 'Bateau'
UNION ALL SELECT 'j''ai un super Panda'
UNION ALL SELECT 'World of bateau'
UNION ALL SELECT 'Pingouin'
)
,
Mots AS (
SELECT
Label,
LEFT(Label, CHARINDEX(' ', Label + ' ')) AS Mot,
SUBSTRING(Label, CHARINDEX(' ', Label + ' ') + 1, 8000) AS Reste
FROM data
UNION ALL
SELECT
label,
LEFT(Reste, CHARINDEX(' ', Reste + ' ')) ,
SUBSTRING(Reste, CHARINDEX(' ', Reste + ' ') + 1, 8000)
FROM Mots
WHERE LEN(Reste) > 2
)
,res AS(
SELECT Label, Mot, COUNT(*) OVER(PARTITION BY Mot) AS NbMots
FROM Mots
)
SELECT *
FROM res
WHERE NbMots > 1 |
Partager