SQL text before the nth match?

Question

Using SQL, I would like to return all the text before the third slash in the column

So

/one/two/three/whatever/testing 

will return:

 /one/two/three 

Any quick and dirty way to do this in SQL (specifically MS T-SQL in MS SQL 2005+)?

Answer 1

Since you said “quick and dirty”, I assume that this very fast and very dirty decision will not get a ton of votes. The SQL below uses several SUBSTRING() functions to find the third slash:

 DECLARE @str VARCHAR(50) SET @str = '/one/two/three/whatever/testing' SELECT SUBSTRING(@str, 0, CHARINDEX('/', @str, CHARINDEX('/', @str, CHARINDEX('/', @str, CHARINDEX('/', @str, 0) + 1) + 1) + 1)) 

You can see a working example here .

Answer 2

Try adding a feature.

 /* Example: SELECT dbo.CHARINDEX2('a', 'abbabba', 3) returns the location of the third occurrence of 'a' which is 7 */ CREATE FUNCTION CHARINDEX2 ( @TargetStr varchar(8000), @SearchedStr varchar(8000), @Occurrence int ) RETURNS int AS BEGIN DECLARE @pos INT, @counter INT, @ret INT set @pos = CHARINDEX(@TargetStr, @SearchedStr) set @counter = 1 if @Occurrence = 1 set @ret = @pos else begin while (@counter < @Occurrence) begin select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1) set @counter = @counter + 1 set @pos = @ret end end RETURN(@ret) end 

Then refer to the function as such ...

 SELECT SUBSTRING('/one/two/three/whatever/testing', 0, dbo.CHARINDEX2('/', '/one/two/three/whatever/testing', 3)) 

Check out the article here for a better view :)

Answer 3

 CREATE FUNCTION dbo.CharIndex2 (@expressionToFind VARCHAR(MAX), @expressionToSearch VARCHAR(MAX), @instance INT) RETURNS INT BEGIN DECLARE @Position INT DECLARE @i INT = 1 WHILE @i <= @instance BEGIN SET @Position = CHARINDEX(@expressionToFind,@expressionToSearch,COALESCE(@Position+1,1)) SET @i += 1 END RETURN @Position END GO