时间戳

首页 > 解决方案 > How can I remove extra placeholders in this string in SQL?

sql-server - How can I remove extra placeholders in this string in SQL?

问题描述

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?

标签： sql-servertsql

解决方案

如果模式不是 4-3，则一种选择是使用 PARSENAME() 和 CONCAT()

例子

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

如果模式是 4-3... left()/right()

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

两者都会返回

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

最后一个选项，只是为了好玩......使用几个 replace()

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

推荐阅读