Function to find the nth word in a string

I have the following access function that looks for the nth word in a string following a '-' (hyphen). I would like to know how to convert this in SQL Server. Thanks for you help.


Public Function GetCSWord(ByVal s, Indx As Integer, Optional strdelimiter = "-")
'Returns the nth word in a specific field
On Error Resume Next
GetCSWord = Split(s, strdelimiter)(Indx - 1)
End Function

Open in new window

LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
Try this

This example will return %25c because it's the 3rd "WORD" delimited by |.
select [dbo].[fn_FindNthWord]('a|%25basdf|%25c|%25d','|',3)

if object_id('[dbo].[fn_FindNthWord]') is not null
     drop function [dbo].[fn_FindNthWord]
go
create function [dbo].[fn_FindNthWord](@DelimitedString nvarchar(max), @Delimiter nvarchar(32),@nth int)
returns varchar(max)
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
declare @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return (select theValue from @Values where ident = @nth)
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_FindNthWord, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_FindNthWord]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_FindNthWord]('a','|')
select * from [dbo].[fn_FindNthWord]('a basdf c d',' ')
*/
GO
 
select [dbo].[fn_FindNthWord]('a|%25basdf|%25c|%25d','|',3)

Open in new window

0
 
chapmandewCommented:
declare @nth int
declare @string varchar(1000)

set @string = 'something'

select substring(@string, @nth, 1)
0
 
Kevin CrossChief Technology OfficerCommented:
You could create a split function like the one you have in VB.

Here is an example of solution -- http:Q_23786715.html

The occurrenceID is what you use as the index.  Since this is 1 based, you would just check for Indx instead of Indx - 1 but same concept.  Guess you could alter code to start at 0 index too if you wanted. :)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Kevin CrossChief Technology OfficerCommented:
To make the delimiter optional like in your code, for the @Delimiter paramater change its declaration to this:

@Delimiter VARCHAR(5) = '-'
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
MWvisa1

Can you put that into a function? that uses position, then the hyphen? I'm not sure i totally undersand that solution. thanks.
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
Chapmandew,

can you put your solution into a created function?
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
so should create this numbers table? and can i use that function to query any table once created?
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
i would like to use it as such:

select [dbo].[fn_FindNthWord] (field_name,4)

so that i get the 4th word. but i really only care about the hyphen. i guess i could use that function like this:

select [dbo].[fn_FindNthWord] (field_name,-,4) from table_name

is that a correct assumption?
0
 
BrandonGalderisiCommented:
Yes you need to create the numbers view first.  Unless you have a numbers table.  Then yes, you can either hard code the '-' into the function as a delimiter or pass it in in single quotes like below.

select [dbo].[fn_FindNthWord] (field_name,'-',4) from table_name

I would suggest passing the parameter that way you're not making it so specific to this one use.
0
 
Kevin CrossChief Technology OfficerCommented:
I was going to tell you would I copy my solution from the link I sent and just amend to take in your position paramenter as I didn't want to pirate Brandon's code, but he has joined the discussion so you can see his code for yourself.

What he posted was what I was linking to.  You create a split function and then just add your start position to it.  Since the split function we had was creating results as individual records with row ids, you just select the row id = @nth.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.