SQL text before then Nth match?

问题:

Using SQL I'd like to return all text before the 3rd forward slash in a column

so

/one/two/three/whatever/testing

would return:

/one/two/three

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

回答1:

Since you said "quick and dirty", I'm assuming that this very quick and very dirty solution won't receive a bunch of down votes. The SQL below uses multiple 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.

回答2:

Try adding the function

/*
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 reference the function as such...

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

Check out an article here for a better look :)

回答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