By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Nth location of string

P: 2
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?
Jul 15 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
You can try this

Expand|Select|Wrap|Line Numbers
  1. select 
  2. 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$')
  3.  
This is a long one. If you want the N as variable, you might want to just parse the string.

Happy Coding!!!


--- CK
Jul 16 '09 #2

Delerna
Expert 100+
P: 1,134
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
  1. CREATE proc At @String varchar(100),@Find varchar(100),@N int
  2. as
  3. declare @start int
  4. declare @NthPosition int
  5. set @start=1
  6.  
  7. while @N>0
  8. Begin
  9.    set @NthPosition=charindex(@Find,@String,@start)
  10.    set @start=@NthPosition+1
  11.    set @N=@N-1
  12. end
  13.  
  14. select @NthPosition
  15. GO
  16.  
  17.  
and then call it with the parameters

Expand|Select|Wrap|Line Numbers
  1. exec at 'one$two$three$four$five$','$',4
  2.  
Jul 16 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.