Robin Hames

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

Friday, August 21, 2009

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

Posted On Friday, August 21, 2009 9:16 AM | Comments (4) | Filed Under [ SQL Server ]

Powered by: