Skip to content

Instantly share code, notes, and snippets.

@dhmacher
Created October 6, 2020 09:07
Show Gist options
  • Save dhmacher/807654b72423fd730400a2457886d72a to your computer and use it in GitHub Desktop.
Save dhmacher/807654b72423fd730400a2457886d72a to your computer and use it in GitHub Desktop.
-- TRANSLATE() and STRING_SPLIT() work with SQL Server 2016+, STRING_AGG() with 2017+.
-- language_id=1033 is English (simplified)
SELECT STRING_AGG(REPLACE(TRANSLATE(s.[value] COLLATE database_default, '():.,-/='';', '**********'), '*', ''), ' ')
FROM sys.messages AS msg
CROSS APPLY STRING_SPLIT(msg.[text], ' ') AS s
WHERE msg.language_id=1033
--AND s.[value] COLLATE database_default NOT IN ('the', 'is', 'to', 'not', 'a', 'for', 'in', 'be', 'of', 'or', 'cannot', 'and')
AND s.[value] COLLATE database_default NOT LIKE '%[%@=]%'
GROUP BY msg.message_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment