Monday, October 5, 2009

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

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...

Contributors