Nth location of string

ljpitre

Assuming the following: I do not have privileges to write a user defined function in the database and I'm not allowed to use VB, Using strickly SQL and built-in functions,

I need to get the starting column of the Nth occurance of a string contained in another string.

Example:

AT('one$two$three$four$five$', '$', 2)

returns the starting position in the first string of the second string for the second occurnace.

Is there a way to do this with out writing userdefined function or external calls?

ck9663

You can try this

Expand|Select|Wrap|Line Numbers

 
select 

charindex('$',substring('one$second$three$four$five$',charindex('$','one$second$three$four$five$')+1,len('one$second$three$four$five$'))) + charindex('$','one$second$three$four$five$')

This is a long one. If you want the N as variable, you might want to just parse the string.

Happy Coding!!!

--- CK

Delerna

Just something extra to consider in addition to what CK wrote

Your question is not very clear.
For example is the string part of a recordset and therefore
the solution needs to work on a set of strings?

The way you phrase the question suggests to me that
...You are working with a single string and not a recordset of strings
...You want something like a UDF but it can't be a UDF
...You want to pass the string, the string to find and the Nth occurence of it

So going from your desciption alone
you can create a Stored Procedure

Expand|Select|Wrap|Line Numbers

 
CREATE proc At @String varchar(100),@Find varchar(100),@N int

as

declare @start int

declare @NthPosition int

set @start=1
 
while @N>0

Begin

   set @NthPosition=charindex(@Find,@String,@start)

   set @start=@NthPosition+1

   set @N=@N-1

end
 
select @NthPosition

GO

and then call it with the parameters

Expand|Select|Wrap|Line Numbers

exec at 'one$two$three$four$five$','$',4

Nth location of string

re: Nth location of string

re: Nth location of string

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about