从存储为varchar的数字中去除开头的零 [英] Strip leading zero from number stored as varchar

问题描述

我正在尝试从SQL Server中的表查询.我无权更新表或创建自己的表或视图.

I am trying to query from a table in SQL server. I do not have the rights to update tables or create my own tables or views.

我设法退还了非常接近我所需要的东西.我需要返回表的所有列，并更改特定列中的某些值.

I have managed to return something very close to what I need. I need to return all the columns of a table with some of the values in a specific column changed.

我现在拥有的:

select *, RIGHT(STN1,len(STN1)-1) AS NEW_STNO
FROM WOPSEGS0
WHERE len(STN1) > 0 AND SUBSTRING(STN1,1,1) = '0'

这是需要进行的更改.因此，如果STN1为08，则需要将其更改为8.此代码返回该结果，但它忽略了所有未更改的内容(STN1 = 25).我的想法是要以某种方式将更新函数嵌入到select语句中，以返回通常只是通过使用更新表函数创建的新表.

This is the change that needs to be made. So, if a STN1 is 08, I need to change it to 8. This code returns that result, but it omits everything that hasn't been changed ex(STN1 = 25). My thought was to somehow embed an update function inside of a select statement to return what normally would just be the new table I created by simply using the update table function.

我所拥有的:

STN1       STCD
00         AZ
00         AZ
01         CA
12         NV

我需要什么:

STN1       STCD
0          AZ
0          AZ
1          CA
12         NV

我可以退货:

STN1       STCD
0          AZ
0          AZ
1          CA

推荐答案

我认为您只需要一个case表达式来确定是否剥离0.

I think you just need a case expression to determine whether to strip the 0 or not.

declare @WOPSEGS0 table (STN1 varchar(2), STCD varchar(2))

insert into @WOPSEGS0 (STN1, STCD)
  select '00', 'AZ' union all
  select '00', 'AZ' union all
  select '01', 'CA' union all
  select '12', 'NV'

select *
  , case when SUBSTRING(STN1,1,1) = '0' then RIGHT(STN1,len(STN1)-1) else STN1 end as NEW_STNO
FROM @WOPSEGS0
WHERE len(STN1) > 0 --AND SUBSTRING(STN1,1,1) = '0'

PS:这是发布SQL问题的首选方法，您可以在其中提供DDL&重现该问题需要DML语句.

PS: This is the preferred method of posting SQL questions where you provide the DDL & DML statements required to reproduce the problem.

这篇关于从存储为varchar的数字中去除开头的零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！