Robin Hames

Hints, tricks and tips relating to MS SQL Server and .NET
posts - 14 , comments - 45 , trackbacks - 0

Trim Leading Zeros from a string in SQL Server

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

Print | posted on Friday, August 21, 2009 9:16 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Trim Leading Zeros from a string in SQL Server

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
3/8/2010 3:14 PM | Bill Gibbs
Gravatar

# re: Trim Leading Zeros from a string in SQL Server

great! thanks!
8/19/2010 2:53 PM | a
Gravatar

# re: Trim Leading Zeros from a string in SQL Server

That was clever!
10/11/2012 5:26 PM | Pablo
Gravatar

# re: Trim Leading Zeros from a string in SQL Server

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
7/19/2013 5:46 PM | JR
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: