?
Solved

Function to find the nth word in a string

Posted on 2008-10-30
10
Medium Priority
?
1,477 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:Auerelio Vasquez
  • 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 61

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 61

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:Auerelio Vasquez
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:Auerelio Vasquez
ID: 22844414
Chapmandew,

can you put your solution into a created function?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 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:Auerelio Vasquez
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:Auerelio Vasquez
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 61

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 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