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.
This question is related to
sql
sql-server
tsql
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))
select CASE
WHEN TRY_CONVERT(bigint,Mtrl_Nbr) = 0
THEN ''
ELSE substring(Mtrl_Nbr, patindex('%[^0]%',Mtrl_Nbr), 18)
END
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))
To remove leading 0, You can multiply number column with 1 Eg: Select (ColumnName * 1)
select replace(replace(rtrim(replace(replace(replace(replace(ltrim(replace(replace([COLUMN],' ','[Ltrim]'),[Ltrim],' ')),' ',[Ltrim]),'[Ltrim]',' '),' ','[Rtrim]'),[Rtrim],' ')),' ',[Rtrim]),'[Rtrim]',' ') As Result
This is a Sql script that emulates the functionality of the TRIM command in tsql prior to 2017, its basically the same as the other recomendateions,but the others replace with a single uncommon character which still can occur, '[Rtrim]' or '[Ltrim]' could still occur in text, but replacing hat with a unique text, for example a Guid would solve that problem.
i havent tested it regarding speed
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
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
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.
select replace(ltrim(replace(ColumnName,'0',' ')),' ','0')
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
you can try this
SELECT REPLACE(columnname,'0','') FROM table
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.
select CASE
WHEN TRY_CONVERT(bigint,Mtrl_Nbr) = 0
THEN ''
ELSE substring(Mtrl_Nbr, patindex('%[^0]%',Mtrl_Nbr), 18)
END
You can use this:
SELECT REPLACE(LTRIM(REPLACE('000010A', '0', ' ')),' ', '0')
select ltrim('000045', '0') from dual;
LTRIM
-----
45
This should do.
To remove leading 0, You can multiply number column with 1 Eg: Select (ColumnName * 1)
I had the same need and used this:
select
case
when left(column,1) = '0'
then right(column, (len(column)-1))
else column
end
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.
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
select ltrim('000045', '0') from dual;
LTRIM
-----
45
This should do.
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
you can try this
SELECT REPLACE(columnname,'0','') FROM table
select replace(replace(rtrim(replace(replace(replace(replace(ltrim(replace(replace([COLUMN],' ','[Ltrim]'),[Ltrim],' ')),' ',[Ltrim]),'[Ltrim]',' '),' ','[Rtrim]'),[Rtrim],' ')),' ',[Rtrim]),'[Rtrim]',' ') As Result
This is a Sql script that emulates the functionality of the TRIM command in tsql prior to 2017, its basically the same as the other recomendateions,but the others replace with a single uncommon character which still can occur, '[Rtrim]' or '[Ltrim]' could still occur in text, but replacing hat with a unique text, for example a Guid would solve that problem.
i havent tested it regarding speed
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
You can use this:
SELECT REPLACE(LTRIM(REPLACE('000010A', '0', ' ')),' ', '0')
I had the same need and used this:
select
case
when left(column,1) = '0'
then right(column, (len(column)-1))
else column
end
Source: Stackoverflow.com