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

Calling Dataverse Web API in PowerShell using Client Credentials

Windows Server 2008R2 VMs Shut Down After 1 to 2 Hours