CODEWITHSUNDEEP
sqlsql-server
Matthew Beatty
Matthew Beatty

Reputation: 2812

How would I get the longest word in a table column in SQL

How would I get longest word in a string/sentence stored in a given in any row of column of given a table in SQL?

Should return single result row with the length of the word and what the word is.

Motivation: need to know longest search term for search optimization on a highly static data source

Upvotes: 0

Views: 4505

Answers (2)

CPMunich
CPMunich

Reputation: 725

You can add a persisted computed column to your table which uses the LEN-Function to store the Length of the desired column. When you then put an index on the computed column the

 SELECT MAX(<computedcolumn_name>) AS maxLength FROM yourTable

will be very efficient.

Upvotes: 0

Matthew Beatty
Matthew Beatty

Reputation: 2812

I was able to get the answer I neeeded using this, simply change [SomeCodeTable] and do any necessary modifications to the Punctuation to Whitespace replacement line

With Strings as (Select [Description] as String From [SomeCodeTable]),,
 Filtered as (Select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(String, '&', ' '), '<', ' '), '>', ' '), '--', ' '), '(', ' '), ')', ' '), ';', ' '), ',', ' '), '/', ' '), '-', ' '), '.', ' ') as String From Strings),
 XmlWords as (Select Cast ('<M>' + Replace(String, ' ', '</M><M>') + '</M>' AS XML) AS Data from Filtered)
SELECT TOP 1 LEN(Split.a.value('.', 'VARCHAR(100)')) as LongestWordLength, Split.a.value('.', 'VARCHAR(100)') as Word
FROM   XmlWords AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
ORDER  BY Len(Split.a.value('.', 'VARCHAR(100)')) DESC

Code from this previous SO answer was helpful in writing this: https://stackoverflow.com/a/28876781

Upvotes: 1

Related Questions