How can I remove extra placeholders in this string in SQL?

Question

I have a column of strings that look like the following.

1991-001
1991-030
1994-003

and want to output these strings by removing the placeholder 0's to this

1991-1
1991-30
1994-3

How would I go about doing this dynamically for each row in SQL Server?

Answer 1

One option is use PARSENAME() and CONCAT() if the pattern is not 4-3

Example

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values 
 ('1991-001')
,('1991-030')
,('1994-003')

Select *
      ,NewValue = concat(
                          try_convert(int,parsename(replace(SomeCol,'-','.'),2))
                         ,'-'
                         ,try_convert(int,parsename(replace(SomeCol,'-','.'),1))
                         )
 From @YourTable A

If the pattern is 4-3... left()/right()

Select *
      ,NewValue = concat(
                          try_convert(int,left(SomeCol,4))
                         ,'-'
                         ,try_convert(int,right(SomeCol,3))
                         )
 From @YourTable A

Both would return

SomeCol     NewValue
1991-001    1991-1
1991-030    1991-30
1994-003    1994-3

One last option, just for fun ... use a couple of replace()

Select *
      ,NewValue = replace(replace(SomeCol,'-0','-'),'-0','-')
 From @YourTable A

Answer 2

You can try the below query.

create table testtable (name varchar(20))
insert into testtable values ('1991-001'), ('1991-030')

Select Convert(Varchar(30), try_cast(ColA as int)) + '-' +  Convert(Varchar(30),try_cast(ColB as int)) from(
SELECT SUBSTRING(name, 1, CHARINDEX('-', name)-1) AS ColA,
       SUBSTRING(name, CHARINDEX('-', name) + 1, 1000) AS ColB
FROM   testtable
WHERE CHARINDEX('-', name) > 0
)a

The output is as shown below.

(No column name)
----------------
1991-1
1991-30

This solution assumes both sides of the - is number otherwise it will give null as the output.