blocks|key|4249004|text|如果只有第一个数字是0，且该列是数值类型，则可以在连接时将其转换为int：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4249005|这将删除开头的每个0|4249006|FROM+table1+inner+join+table2+on+CAST(table1.col+as+int)+=+table2.col|code-block|syntax|javascript|4249007|您还可以将其子串：|4249008|FROM+table1+inner+join+table2+on+substring(table1.col,+2,+9)+=+table2.col|4249009|这两个选项是在连接时完成的，但是，如果将列的数据类型转换为int，则可以只修改表以更新列中的每个值，但它将从开头删除每个0，这与第一个选项非常相似：|4249010|ALTER+TABLE+table1
ALTER+COLUMN+col+int;|4249011|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

If only the first digit is a 0 and the column is a numeric type you can convert it to int at the time of joining: 

This will remove every 0 at the beginning

<pre><code>FROM table1 inner join table2 on CAST(table1.col as int) = table2.col
</code></pre>

You can also substring it:

<pre><code>FROM table1 inner join table2 on substring(table1.col, 2, 9) = table2.col
</code></pre>

Those two options are to be done at the moment of joining, however, you can just alter the table to update every value in the column if you convert the column's data type to int, but it will remove every 0 from the beginning, much like the first option: 

<pre><code>ALTER TABLE table1
ALTER COLUMN col int;
</code></pre>

blocks|key|4249024|text|如果列是varchar，并且始终有10个以0开头的数字？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4249025|那么RIGHT可能就是合适的选择。|offset|length|4249026|declare+@T1+table+(id1+int+identity(1,1)+primary+key,+var_10+varchar(10));
insert+into++@T1+(var_10)+values+('0123456789');

declare+@T2+table+(id2+int+identity(1,1)+primary+key,+var_09+varchar(9));
insert+into++@T2+(var_09)+values+('123456789');

select+*+
from+@T1+t1
join+@T2+t2+on+t2.var_09+=+right(t1.var_10,+9);|code-block|syntax|javascript|4249027|但是如果你对此不太确定，那么我会建议TRY_CONVERT+(或TRY_CAST)将它们都转换为一个整数。|4249028|select+*+
from+@T1+t1
join+@T2+t2+on+try_convert(int,t2.var_09)+=+try_convert(int,t1.var_10);|4249029|与CONVERT相比，TRY_CONVERT的优点在于，如果某些数据不包含数字，SQL就不会在该数据上崩溃。|4249030|但是f.e.CONVERT(int,'123FOOBAR')会使SQL崩溃。|style|CODE|4249031|而TRY_CONVERT(int,'123FOOBAR')只会返回一个NULL。|4249032|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/functions/right-transact-sql|1|https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql^0|0|2|5|0|0|0|I|B|1|0|0|0|6|O|0|1|S|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@$D|18|E|19|1|1A]]|A|$]]|$1|F|3|G|5|H|7|1B|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1C|8|@]|9|@$D|1D|E|1E|1|1F]]|A|$]]|$1|M|3|N|5|H|7|1G|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1H|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1I|8|@$D|1J|E|1K|S|T]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1L|8|@$D|1M|E|1N|S|T]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]|14|$5|Z|10|11|A|$12|15]]]]

If the column is a varchar and has always 10 digits that start with a 0 ?

Then <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/right-transact-sql" rel="nofollow noreferrer">RIGHT</a> might be right for it.

<pre><code>declare @T1 table (id1 int identity(1,1) primary key, var_10 varchar(10));
insert into @T1 (var_10) values ('0123456789');

declare @T2 table (id2 int identity(1,1) primary key, var_09 varchar(9));
insert into @T2 (var_09) values ('123456789');

select * 
from @T1 t1
join @T2 t2 on t2.var_09 = right(t1.var_10, 9);
</code></pre>

But if you're not too sure about it, then I would suggest to <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql" rel="nofollow noreferrer">TRY_CONVERT</a> (or TRY_CAST) them both to an INT.

<pre><code>select * 
from @T1 t1
join @T2 t2 on try_convert(int,t2.var_09) = try_convert(int,t1.var_10);
</code></pre>

A TRY_CONVERT has the benifit over a CONVERT that if some of the data doesn't contain numbers that the SQL wouldn't crash on that. 
But f.e. a <code>CONVERT(int,'123FOOBAR')</code> would crash the SQL. 
While a <code>TRY_CONVERT(int,'123FOOBAR')</code> would just return a NULL.

blocks|key|4249045|text|如果将其用于连接，则转换为int可能更有效|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4249046|FROM+table1+
inner+join+table2+
+++on+CAST(table1.col+as+int)+=+CAST(table2.col+as+int)|code-block|syntax|javascript|4249047|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you are using it for a join then cast to int is probably more efficient 

<pre><code>FROM table1 
inner join table2 
 on CAST(table1.col as int) = CAST(table2.col as int)
</code></pre>

Currently, I have a column with 10 digits with leading '0' on all the records and I want to remove that '0' and keep 9 digits only even if it's start with a zero. I want to later join that table with another table in that column and the other table has 9 digits on all record and some start with 0.

What is the best way to update a table by removing only one leading zero from numeric values in a column in SQL Server database

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

目前，我在所有记录上都有一个包含10位前导'0‘的列，我想删除那个'0’，只保留9位，即使它以0开头。我想稍后将该表与该列中的另一个表连接起来，而另一个表的所有记录都有9位数，有些从0开始。

问通过从SQL Server数据库列的数值中仅删除一个前导零来更新表的最佳方法是什么
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过从SQL Server数据库列的数值中仅删除一个前导零来更新表的最佳方法是什么EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问通过从SQL Server数据库列的数值中仅删除一个前导零来更新表的最佳方法是什么
EN