Tuesday, February 16, 2010

SQL Server 2005:Function to remove prefixes 0,-,+,*,.. etc

This function will help you to remove prefixes from given string
"
CREATE FUNCTION [dbo].[remove_prefixes]
(
@str varchar(600)--The String from which you want to remove prefix
)
RETURNS varchar(600)
AS

BEGIN

DECLARE @strLen int
SET @strLen = LEN( @str );
IF @strLen < 1
BEGIN
RETURN @str
END

-- Prefix
DECLARE @prefix char
SET @prefix = LEFT( @str, 1 )


-- '+ or -' as prefix
IF @prefix = '+ or - or * or & '
BEGIN
SET @str = RIGHT( @str, @strLen - 1 )
END

SET @strLen = LEN( @str )
SET @prefix = LEFT( @str, 1 )

-- '0' as prefix
IF @prefix = '0'
BEGIN
SET @str = RIGHT( @str, @strLen - 1 )
END

return @str

END "
For Example
select [dbo].[mpc_remove_prefixes]('*Prefix test')
select [dbo].[mpc_remove_prefixes]('0Prefix test')

Result

Both statement Returns 'Prefix test' as a result

No comments: