How to remove first N specific characters if they all are zeros

Question

I have this type of strings

01/CBA/1234567890
02/ABC/0000969755
06/DEF/0000000756

I want to remove the zeroes and get following output

01/CBA/1234567890
02/ABC/969755
06/DEF/756

How can i do that ? I though about combination of RIGHT, LEFT, CHARINDEX, SUBSTRING functions but have no idea how to combine them.

Any idea ?

http://stackoverflow.com/questions/92093/removing-leading-zeroes-from-a-field-in-a-sql-statement — Kostia Shiian, Feb 13 '15 at 16:22

score 1 · Accepted Answer · answered Feb 13 '15 at 16:23

1

You can use something like this:

SUBSTRING(myString, PATINDEX('%[^0]%', myString+'.'), LEN(myString))

answered Feb 13 '15 at 16:23

Rubix Rechvin

571
3
16

1

Thank you this works :-) declare @str as varchar(20) = '02/ABC/0000969755' select LEFT(@str,7) + SUBSTRING(RIGHT(@str,10), PATINDEX('%[^0]%', RIGHT(@str,10)+'.'), LEN(RIGHT(@str,10))) but why there is that dot ?:P – Muflix Feb 13 '15 at 20:26

Sarath Subramanian · Answer 2 · 2015-02-13T16:48:18.803

Try this one

Method 1

DECLARE @STR VARCHAR(100)= '02/ABC/0000969755'

SELECT SUBSTRING(@STR,0,LEN(@STR) - CHARINDEX('/', REVERSE(@STR)) + 2)+
 CAST(CAST(REVERSE(SUBSTRING(REVERSE(@STR),0,CHARINDEX('/',REVERSE(@STR))))AS INT)AS VARCHAR(20))

Click here to view result

Method 2

DECLARE @STR VARCHAR(100)= '02/ABC/0000969755'


;WITH CTE AS
(
    -- Convert to rows
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RNO,
    LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'STRING' 
    FROM  
    (        
         SELECT CAST ('<M>' + REPLACE(@STR, '/', '</M><M>') + '</M>' AS XML) AS Data      
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
,CTE2 AS
(
   -- Convert to int and then varchar
   SELECT RNO,CASE WHEN RNO = 3 THEN CAST(CAST(STRING AS INT)AS VARCHAR(40)) ELSE STRING END STRR
   FROM CTE
)
-- Convert back to / separated values
SELECT  SUBSTRING(
         (SELECT  '/ ' + STRR
          FROM CTE2             
          ORDER BY RNO
          FOR XML PATH('')),2,200000) STRING

Click here for working solution

See the first method. Its not complicated. BTW I just gave a way of executing these in different methods. @Muflix — Sarath Subramanian, Feb 14 '15 at 02:20

score 1 · Answer 3 · answered Feb 13 '15 at 16:41

Below Query will help you

DECLARE @var VARCHAR(500) = '06/DEF/0000000756'
SELECT 
  LEFT(@var, CHARINDEX('/',@var))  + 
  LEFT(REPLACE(@var, LEFT(@var, CHARINDEX('/',@var)),''), 
      CHARINDEX('/',REPLACE(@var, LEFT(@var, CHARINDEX('/',@var)) ,''))) 
                                   +
 CONVERT(varchar, CONVERT(numeric(18,0),REPLACE(REPLACE(@var,LEFT(@var, 
        CHARINDEX('/',@var)),''),Left(REPLACE(@var,LEFT(@var,
        CHARINDEX('/',@var)) ,''), 
        CHARINDEX('/',REPLACE(@var,LEFT(@var,
        CHARINDEX('/',@var)),''))),'')))

score 1 · Answer 4 · answered Feb 13 '15 at 19:13

1

use left and right string function

DECLARE @str varchar(20)='06/DEF/0000000756'
SELECT left(@str,7)+convert(varchar(20),convert(int,right(@str,10)))

enter image description here

answered Feb 13 '15 at 19:13

Dudi Konfino

1,126
2
13
24

How to remove first N specific characters if they all are zeros

4 Answers4