Removing leading zeroes from a field in a SQL statement

Question

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

User · Answer

I had the same need and used this    select      case          when left column 1     0           then right column   len column -1            else column        end

User · Answer

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

User · Answer

you can try this  SELECT REPLACE columnname  0      FROM table

User · Answer

you can try this  SELECT REPLACE columnname  0      FROM table

User · Answer

select replace ltrim replace ColumnName  0             0

User · Answer

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

User · Answer

To remove leading 0  You can multiply number column with 1 Eg  Select  ColumnName   1

User · Answer

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

User · Answer

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

User · Answer

select substring ColumnName  patindex     0    ColumnName   10

User · Answer

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 amp fiddle 14c2dca84aa28f2a7a1fac59c9412d48

User · Answer

I had the same need and used this    select      case          when left column 1     0           then right column   len column -1            else column        end

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

select ltrim  000045    0   from dual   LTRIM ----- 45   This should do

User · Answer

You can use this   SELECT REPLACE LTRIM REPLACE  000010A    0               0

User · Answer

You can use this   SELECT REPLACE LTRIM REPLACE  000010A    0               0

User · Answer

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

User · Answer

select replace ltrim replace ColumnName  0             0

User · Answer

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

User · Answer

select CASE          WHEN TRY CONVERT bigint Mtrl Nbr    0            THEN               ELSE substring Mtrl Nbr  patindex     0    Mtrl Nbr   18         END

User · Answer

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

User · Answer

To remove leading 0  You can multiply number column with 1 Eg  Select  ColumnName   1

User · Answer

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

User · Answer

select substring ColumnName  patindex     0    ColumnName   10

User · Answer

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

User · Answer

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 amp fiddle 14c2dca84aa28f2a7a1fac59c9412d48

User · Answer

select ltrim  000045    0   from dual   LTRIM ----- 45   This should do

User · Answer

select substring ColumnName  patindex     0    ColumnName   10

User · Answer

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

User · Answer

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

User · Answer

select substring ColumnName  patindex     0    ColumnName   10

User · Answer

select CASE          WHEN TRY CONVERT bigint Mtrl Nbr    0            THEN               ELSE substring Mtrl Nbr  patindex     0    Mtrl Nbr   18         END

[sql] Removing leading zeroes from a field in a SQL statement

Examples related to sql

Examples related to sql-server

Examples related to tsql