sql server function charindex at n position

Charindex at n position
To find the charindex of an expression “_” in n position say 3rd occurancein a given word 'xx_xxx_xx_xx'
Result : 10
SELECT dbo.fn_charIndex('_',3,' xx_xxx_xx_xx')
CREATE FUNCTION [dbo].[fn_charIndex](@Expression varchar(256),@nPos int,@Word varchar(2000))


returns int

as

begin

Declare @ret int,@strt int, @strLen int

set @ret = 0

set @strt = 0

while not @strt = @npos

begin

select @ret = charindex(@Expression,@Word,@ret+1)

set @strt = @strt +1

IF @ret = 0

Break

ELSE

Continue

end

return @ret

end



Post a Comment