CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
craig
craig

Reputation: 26272

SQL Server - remove leading and trailing zeros

I need to remove leading and trailing zeros from a dataset:

WITH

data as (
    select 0.10 ITEM
    union all
    select -1.00
    union all
    select 0.20
    union all
    select 2.00
    union all
    select 508000015.00
)

So that the result resemble:

ITEM
.1
-1
.2
2
508000015

Is there a more-concise or more-efficient way to do so 

SELECT  case
          # if item isn't an integer, trim 0 from left and right 
          when item % 1 = item then REPLACE(RTRIM(LTRIM(REPLACE(item,'0',' '))),' ','0') 
          else cast(cast(item as int) as varchar) 
        end VALUE
FROM    data

Upvotes: 1

Views: 860

Answers (4)

BrownRedHawk
BrownRedHawk

Reputation: 113

I think this may be simplier. Apologies for being a tad lazy, but I think you'll get the idea.

WITH data as (
 select 0.10 ITEM
 union all
 select -1.00
 union all
 select 0.20
 union all
 Select 2.00
 union all
 select 508000015.00
 union all
 select 508015
 union all
 select 50815)

set1 as (Select
case when LEN(item) > 6 then cast(cast(item as numeric) as varchar)
else cast(cast(item as float) as varchar)
end item
from data)

select
case
    when item like '0.%' then RIGHT(item,len(item)-1) else item
end As item
from set1

Upvotes: 0

David Rushton
David Rushton

Reputation: 5040

If you are storing the data in a numeric(12, 2) column it does not matter. SQL Server will always present the content with 2 decimal places.

SELECT
    CAST(1.0 AS NUMERIC(12, 2))     AS [NoDp],
    CAST(1.0 AS NUMERIC(12, 2))     AS [1Dp],
    CAST(1.00 AS NUMERIC(12, 2))    AS [2Dp],
    CAST(1.000 AS NUMERIC(12, 2))   AS [3Dp]
;

Returns

NoDp    1Dp     2Dp     3Dp
1.00    1.00    1.00    1.00

If you are interested in formatting the output this is best done in the presentation layer.

Upvotes: 0

Shaneis
Shaneis

Reputation: 1085

Something like this could also work as well:

WITH
data as (
    select 0.10 ITEM
    union all
    select -1.00
    union all
    select 0.20
    union all
    select 2.00
    union all
    select 508000015.00
)
SELECT  *
FROM    data as d
CROSS
APPLY   (SELECT 
                SUBSTRING(CAST(ITEM AS varchar(32)), PATINDEX('0%', CAST(ITEM AS VARCHAR(32))) + 1, LEN(ITEM))) AS f (lead0) -- remove leading 0
CROSS
APPLY   (SELECT 
                SUBSTRING(f.lead0, 1, CASE 
                                        WHEN RIGHT(f.lead0, 2) = '00' THEN LEN(f.lead0) - 3 -- remove decimal point as well
                                        WHEN RIGHT(f.lead0, 1) = '0' THEN LEN(f.lead0) - 1 
                                        ELSE LEN(f.lead0) 
                                    END)) AS s (end0);

Upvotes: 1

M.Ali
M.Ali

Reputation: 69574

Try something like .....

WITH data as (
    select 0.10 ITEM
    union all
    select -1.00
    union all
    select 0.20
    union all
    select 2.00
    union all
    select 508000015.00
)
Select CASE WHEN RIGHT( REPLACE(RTRIM(LTRIM(REPLACE(ITEM,'0',' '))),' ','0'), 1) = '.'
           THEN REPLACE(REPLACE(RTRIM(LTRIM(REPLACE(ITEM,'0',' '))),' ','0'),'.','')
           ELSE REPLACE(RTRIM(LTRIM(REPLACE(ITEM,'0',' '))),' ','0') END
from data

Upvotes: 0

Related Questions