我试图在SQL中创建一个UDF,以返回字符串中最长的单词。我已经创建了以下内容,但我无法使其正常工作。有什么建议吗?
CREATE FUNCTION [dbo].[ufn_Longestword] (@input varchar(255))
RETURNS varchar(100)
AS
BEGIN
declare @pos int
declare @pos2 int
declare @wordpos int
declare @longestword varchar(100)
declare @Letter1 varchar (1)
declare @Letter2 varchar (1)
declare @twords table (
words varchar(100))
SET @pos = 1
WHILE @pos <= len(@input)
BEGIN
SET @Letter1 = substring(@input, @pos, 1)
IF @Letter1 = ' '
BEGIN
SET @pos2 = @pos
WHILE @pos2 <= len(@input)
BEGIN
SET @Letter2 = substring(@input, @pos2, 1)
if @letter2 = ' '
BEGIN
insert into @twords
select SUBSTRING(@input, @pos,@pos2 - @pos)
END
SET @pos2 = @pos2 + 1
END
END
SET @pos = @pos + 1
END
SET @longestword = (select top 1 words from @twords
ORDER BY len(words)desc)
delete from @twords
RETURN @longestword
END
我想弄清楚这两个空格之间的区别,然后把这个词插入到一个临时表中,但它不起作用。
相反,您可以使用它。
DECLARE @str VARCHAR(5000)='aaaa bbbbb cccccccc'
SELECT TOP 1 Split.a.value('.', 'VARCHAR(100)') as longest_Word
FROM (SELECT Cast ('<M>' + Replace(@str, ' ', '</M><M>') + '</M>' AS XML) AS Data) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
ORDER BY Len(Split.a.value('.', 'VARCHAR(100)')) DESC
结果:cccccccc
我发现这是一个解决方案:点击此处
CREATE FUNCTION FN_ex06(@str varchar(8000) )
RETURNS @T TABLE
( position int IDENTITY PRIMARY KEY,
value varchar(8000) ,
length smallint null
)
AS
BEGIN
DECLARE @i int
SET @i = -1
WHILE (LEN(@str) > 0)
BEGIN
SET @i = CHARINDEX(' ' , @str) /* here i used space as delimiter*/
IF (@i = 0) AND (LEN(@str) > 0)
BEGIN
INSERT INTO @T (value, length) VALUES (@str, LEN(@str))
BREAK
END
IF (@i > 1)
BEGIN
INSERT INTO @T (value, length) VALUES (LEFT(@str, @i - 1),
LEN(LEFT(@str, @i - 1)))
SET @str = RIGHT(@str, (LEN(@str) - @i))
END
ELSE
SET @str = RIGHT(@str, (LEN(@str) - @i))
END
RETURN
END
要运行此函数,您可以将其视为表,因为返回是一个表
select max(value) from FN_ex06('karim pentester')
结果是:戊酯
当然,您可以选择返回表中的所有其他列