Geeks With Blogs
Robin Hames Hints, tricks and tips relating to MS SQL Server and .NET

This user defined function is based on a function posted at this very useful site: http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx.

 

I have modified it slightly to handle spaces within the input string; by first replacing any existing spaces with ‘¬’ (this seems to be a suitably obscure character that is never likely to occur in the input string).

 

CREATE FUNCTION RemoveLeadingZerosFromVarChar

(

      -- Add the parameters for the function here

      @inputStr varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

      -- Declare the return variable here

      DECLARE @Result varchar(max)

 

      -- This function works by replacing all '0' characters with a ' ' (space)

      -- character, then using LTRIM to strip all the leading spaces

      -- Finally all ' ' characters remaining are changed back into '0' characters

      -- (this restores any '0' characters indise the input string)

      -- To handle actual spaces within the input string, first any spaces are

      -- replaced with an obscure character that is never likely to occur ('¬')

      -- so that this can be replaced with spaces again at the end.

 

      SELECT @Result = replace(replace(ltrim(replace(

          replace(ltrim(@inputStr), ' ', '¬') -- replace existing spaces with '¬'

          , '0', ' ') -- replace '0' with ' '

          ) -- end of LTRIM to remove leading '0's that have been changed to ' 's

          , ' ', '0') -- change ' ' back to '0'

          , '¬', ' ') -- change '¬' back to ' '

 

      -- Return the result of the function

      RETURN @Result

 

END

GO

Posted on Friday, August 21, 2009 9:16 AM SQL Server | Back to top


Comments on this post: Trim Leading Zeros from a string in SQL Server

# re: Trim Leading Zeros from a string in SQL Server
Requesting Gravatar...
Thanks so much for this logic; need to join a value to an exernal source, and the 0s are interferring - moded this, created new join column, and works a treat...
Cheers,
Bill
Left by Bill Gibbs on Mar 08, 2010 3:14 PM

# re: Trim Leading Zeros from a string in SQL Server
Requesting Gravatar...
great! thanks!
Left by a on Aug 19, 2010 2:53 PM

# re: Trim Leading Zeros from a string in SQL Server
Requesting Gravatar...
That was clever!
Left by Pablo on Oct 11, 2012 5:26 PM

# re: Trim Leading Zeros from a string in SQL Server
Requesting Gravatar...
DROP FUNCTION [dbo].[FN_StripLeading]
GO
CREATE FUNCTION [dbo].[FN_StripLeading] (@string VarChar(128), @stripChar VarChar(1))
RETURNS VarChar(128)
AS
BEGIN
-- http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server
DECLARE @retVal VarChar(128),
@pattern varChar(10)
SELECT @pattern = '%[^'+@stripChar+']%'
SELECT @retVal = CASE WHEN SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) = '' THEN @stripChar ELSE SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) END
RETURN (@retVal)
END
GO
GRANT EXECUTE ON [dbo].[FN_StripLeading] TO PUBLIC
Left by JR on Jul 19, 2013 5:46 PM

Your comment:
 (will show your gravatar)


Copyright © Rhames | Powered by: GeeksWithBlogs.net | Join free