CREATE FUNCTION dbo.trimChar(@char VARCHAR(5),@str VARCHAR(50))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE
@RE VARCHAR(50),
@TEMP VARCHAR(50),
@i INT;
SET @RE='';
SET @i=PATINDEX('%'+@char+'%',@str);
IF LEN(@char)>1 AND @i>0
SET @i=@i+LEN(@char)-1
IF @i-LEN(@char)!=0
SET @TEMP=@str;
ELSE
SET @TEMP=STUFF(@str,1,@i,'');
SET @TEMP=REVERSE(@TEMP);
SET @i=PATINDEX('%'+REVERSE(@char)+'%',@TEMP);
IF LEN(@char)>1 AND @i>0
SET @i=@i+LEN(@char)-1
IF @i-LEN(@char)!=0
BEGIN
SET @RE=REVERSE(@TEMP);
END
ELSE
BEGIN
SET @RE=REVERSE(STUFF(@TEMP,1,@i,''));
END
RETURN @RE;
END
GO
DECLARE
@str VARCHAR(20);
SET @str='02012agd02002d402010';
SELECT dbo.trimChar('0',@str);
SELECT dbo.trimChar('02010',@str);
DROP FUNCTION dbo.trimChar