How to remove 0's between string

Transact-SQL
1

please help me to remove 0 between string

if string is abc00012345
then output is abc12345

if string is abc001234
then output is abc1234

2 
with cteSource AS
( SELECT 'abc00012345' as col1 UNION ALL
  SELECT 'abc001234' UNION ALL
  SELECT 'abd0000230440'
)

SELECT
   PATINDEX('%[1-9]%',col1) as pozOfNumeric
   ,LEFT(col1,PATINDEX('%[1-9]%',col1) - 1) as leftPart
   ,REPLACE(LEFT(col1,PATINDEX('%[1-9]%',col1) - 1),'0','') as leftPartNoZero
   ,REPLACE(LEFT(col1,PATINDEX('%[1-9]%',col1) - 1),'0','')
     + SUBSTRING(col1,PATINDEX('%[1-9]%',col1), LEN(col1) - PATINDEX('%[1-9]%',col1) + 1 ) 
     AS finalString
FROM
  cteSource AS S

output:

pozOfNumeric leftPart leftPartNoZero finalString
7 abc000 abc abc12345
6 abc00 abc abc1234
8 abd0000 abd abd230440

dbfiddle here

3

great.. :slight_smile: thank you again @stepson

4

You're welcome!

5 
with cteSource AS
( SELECT 'abc00012345' as col1 UNION ALL
  SELECT 'abc001234' UNION ALL
  SELECT 'abd0000230440' UNION ALL
  SELECT 'abc12345' UNION ALL
  SELECT 'abc120345'
)
SELECT
    col1,
    CASE WHEN col1_first_0 > col1_first_1_9 OR col1_first_0 = 0 THEN col1
         ELSE STUFF(col1, 4, col1_first_1_9 - col1_first_0, '') END AS col1_new
FROM cteSource
CROSS APPLY (
    SELECT CHARINDEX('0', col1) AS col1_first_0,
        PATINDEX('%[1-9]%', col1) AS col1_first_1_9
) AS alias1
6

Drop table Tokens
create table Tokens
(
Titles char(50)
)

insert into Tokens values('abc000efg')
insert into Tokens values('abc0fg')
insert into Tokens values('rrr00000efg')
insert into Tokens values('abc00yyy')

select a+b as Titles from(
select left(Titles,PATINDEX('%0%', Titles)-1) as a,
substring(substring(Titles,PATINDEX('%0%', Titles),len(Titles)-(PATINDEX('%0%', Titles)-1)),patindex('%[a-z]%',substring(Titles,PATINDEX('%0%', Titles),len(Titles)-(PATINDEX('%0%', Titles)-1))),len(Titles)) as b from Tokens)c

7
Create Data Script 
drop table #data
go 

create table #data
(
data varchar(100)
)
go 

insert into #data select 'abc00012345'
insert into #data select 'abc001234'
insert into #data select 'xswfdf00000wwq2'
go 

select * from #data 
go
SQL 
select REPLACE(data,'0','') from #data 
go
Output

image
image.png734×640 7.29 KB

8

Might as well...

with cteSource AS
( 
SELECT 'abc00012345' as col1 UNION ALL
SELECT 'abc001234' UNION ALL
SELECT 'abd0000230440' Union all
SELECT 'abc12345' UNION ALL
SELECT 'abc120345'
)

select 
col1 
,Rtrim(Replace(STUFF(a.col1, idx, ln,' ') ,0,'')) + SUBSTRING(col1,idx, ln - idx+1 ) [col1_new]
from cteSource  a
cross apply 
	(
	select PATINDEX('%[1-9]%',col1) idx , LEN(col1) ln
	) b