String Function Help
-
Is there a built-in string function, or a simple SQL trick to find the nth instance of a character in a string?
For example, I have a string with several commas in it, and I need to find the index of the 7th occurance of a comma in that string.
It could be easily implemented with a UDF, but I'd rather avoid that if possible.
Thanks,
Jason
The Redneck DBA
-
Not that I know of.
I have a CLR function which will do just that if you need.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Nope, you will need to use a bunch of CHARINDEX calls. At least you don't have to loop. If it helps to think outside of the box, if the number of commas in your string is fixed and there are not more than 13 total, use REVERSE and get to your comma from the other end.
-
I think we can do this.. still check this samples..
http://search.cpan.org/~jzucker/SQL-Statement-1.15/lib/SQL/Statement/Functions.pm
-
You can also use a Numbers table to do string manipulations. If you know know about using a Numbers table, look at this link: Using a Numbers table
Here is a query you can use to find the position of the 7th instance of a character using the Numbers table:
DECLARE @v-2 VARCHAR(100)
SET @v-2 = 'qqqqq,aaaa,sss,ddddd,hhhhh,tttt,kkkk,cccccc,dddddd,sssss' ;
WITH LocateComma
AS (SELECT DISTINCT TOP 7
CHARINDEX(',', @v-2, Number) Indexes
FROM dbo.Numbers
WHERE CHARINDEX(',', @v-2, Number) <> 0
ORDER BY CHARINDEX(',', @v-2, Number))
SELECT TOP 1
Indexes
FROM LocateComma
ORDER BY Indexes DESC
-
You can avoid looping by using a numbers table
declare @s-2 varchar(100)
set @s-2='aa,bb,cc,dd,ee,ff,gg,hh,ii,jj';
with cte as
(select Number,row_number() over(order by Number) as occurance
from Numbers
where Number between 1 and len(@s)
and substring(@s,Number,1)=',')
select Number as [Index]
from CTE
where occurance=7
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply