Removing leading zeroes from a field in a SQL statement

I am working on a SQL query that reads from a SQLServer database to produce an extract file. One of the requirements to remove the leading zeroes from a particular field, which is a simple VARCHAR(10) field. So, for example, if the field contains '00001A', the SELECT statement needs to return the data as '1A'.

Is there a way in SQL to easily remove the leading zeroes in this way? I know there is an RTRIM function, but this seems only to remove spaces.

select substring(ColumnName, patindex('%[^0]%',ColumnName), 10)

select replace(ltrim(replace(ColumnName,'0',' ')),' ','0')

select substring(substring('B10000N0Z', patindex('%[0]%','B10000N0Z'), 20), 
    patindex('%[^0]%',substring('B10000N0Z', patindex('%[0]%','B10000N0Z'), 
    20)), 20)

returns N0Z, that is, will get rid of leading zeroes and anything that comes before them.

I had the same need and used this:

select 
	case 
		when left(column,1) = '0' 
		then right(column, (len(column)-1)) 
		else column 
	  end

You can use this:

SELECT REPLACE(LTRIM(REPLACE('000010A', '0', ' ')),' ', '0')

If you want the query to return a 0 instead of a string of zeroes or any other value for that matter you can turn this into a case statement like this:

select CASE
      WHEN ColumnName = substring(ColumnName, patindex('%[^0]%',ColumnName), 10) 
       THEN '0'
      ELSE substring(ColumnName, patindex('%[^0]%',ColumnName), 10) 
      END

You can try this - it takes special care to only remove leading zeroes if needed:

DECLARE @LeadingZeros    VARCHAR(10) ='-000987000'

SET @LeadingZeros =
      CASE WHEN PATINDEX('%-0', @LeadingZeros) = 1   THEN 
		   @LeadingZeros
	  ELSE 
	       CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10)) 
	  END   

SELECT @LeadingZeros

Or you can simply call

CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10)) 

Here is the SQL scalar value function that removes leading zeros from string:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Vikas Patel
-- Create date: 01/31/2019
-- Description:	Remove leading zeros from string
-- =============================================
CREATE FUNCTION dbo.funRemoveLeadingZeros 
(
	-- Add the parameters for the function here
	@Input varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result varchar(max)

	-- Add the T-SQL statements to compute the return value here
	SET @Result = @Input

	WHILE LEFT(@Result, 1) = '0'
	BEGIN
		SET @Result = SUBSTRING(@Result, 2, LEN(@Result) - 1)
	END

	-- Return the result of the function
	RETURN @Result

END
GO

In case you want to remove the leading zeros from a string with a unknown size.

You may consider using the STUFF command.

Here is an example of how it would work.

SELECT ISNULL(STUFF(ColumnName
                   ,1
                   ,patindex('%[^0]%',ColumnName)-1
                   ,'')
             ,REPLACE(ColumnName,'0','')
             )

See in fiddler various scenarios it will cover

https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=14c2dca84aa28f2a7a1fac59c9412d48

To remove the leading 0 from month following statement will definitely work.

SELECT replace(left(Convert(nvarchar,GETDATE(),101),2),'0','')+RIGHT(Convert(nvarchar,GETDATE(),101),8) 

Just Replace GETDATE() with the date field of your Table.

To remove leading 0, You can multiply number column with 1 Eg: Select (ColumnName * 1)

select CASE
	     WHEN TRY_CONVERT(bigint,Mtrl_Nbr) = 0
		   THEN ''
		   ELSE substring(Mtrl_Nbr, patindex('%[^0]%',Mtrl_Nbr), 18)
	   END

you can try this SELECT REPLACE(columnname,'0','') FROM table

I borrowed from ideas above. This is neither fast nor elegant. but it is accurate.

CASE

WHEN left(column, 3) = '000' THEN right(column, (len(column)-3))

WHEN left(column, 2) = '00' THEN right(a.column, (len(column)-2))

WHEN left(column, 1) = '0' THEN right(a.column, (len(column)-1))

ELSE 

END

select ltrim('000045', '0') from dual;

LTRIM
-----
45

This should do.