Showing posts with label sql server funciton Remove prefixes. Show all posts
Showing posts with label sql server funciton Remove prefixes. Show all posts

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