Solved

Function to find the nth word in a string

Posted on 2008-10-30
10
1,305 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore 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 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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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