Solved

Function to find the nth word in a string

Posted on 2008-10-30
10
1,258 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:basile
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22844269
declare @nth int
declare @string varchar(1000)

set @string = 'something'

select substring(@string, @nth, 1)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22844284
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22844310
To make the delimiter optional like in your code, for the @Delimiter paramater change its declaration to this:

@Delimiter VARCHAR(5) = '-'
0
 
LVL 1

Author Comment

by:basile
ID: 22844398
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
 
LVL 1

Author Comment

by:basile
ID: 22844414
Chapmandew,

can you put your solution into a created function?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22844517
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
 
LVL 1

Author Comment

by:basile
ID: 22844635
so should create this numbers table? and can i use that function to query any table once created?
0
 
LVL 1

Author Comment

by:basile
ID: 22844655
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22844710
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22844740
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now