Solved

Function to find the nth word in a string

Posted on 2008-10-30
10
1,336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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 60

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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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 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: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 60

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…

751 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