SQL - Get Nth Word

Need to find the Nth word in a sentence, try this function.
USE MIGRATION_DB
GO
IF OBJECT_ID (N'dbo.GetNthWord', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetNthWord;
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetNthWord
(
@string varchar(max),
@n int
)
RETURNS varchar(max) AS
BEGIN
DECLARE
@xml xml,
@s nvarchar(max),
@wd varchar(100)

set @xml = '<root><record>' + REPLACE(@string, ' ','</record><record>') +
'</record></root>';

With rs(id, word) AS
(
select ROW_NUMBER ()over( order by (select 1))id , * from(
select
t.value('.','varchar(150)') as [items]
from @xml.nodes('//root/record') as a(t)) data
where len([items]) > 0
)

select @wd=word from rs where id=@n
return(@wd)
END

Comments

Popular posts from this blog

Add User As Local Administrator On Domain Controller

An error occurred while applying security information to

Display ADFS 2.0 Forms Authentication Login Page Instead of Windows Authentication Prompt