blocks|key|407505|text|一个可能的选项是TRIM()函数(需要Server+2017%2B+)。默认情况下，TRIM()函数从字符串的开头和结尾移除空格字符或其他指定字符，因此需要在正确的位置(第1和第10)放置一个附加字符(示例中的#)，然后删除它：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|407506|SELECT+
+++REPLACE(
++++++TRIM('0'+FROM+'#'+%2B+SUBSTRING(PhoneNo,+1,+10)+%2B+'#'+%2B+SUBSTRING(PhoneNo,+11,+5)),
++++++'#',
++++++''
+++)+AS+PhoneNo+++
FROM+(VALUES
+++('19876543210000'),
+++('198765432100'),
+++('1987653210'),
+++('19876543210'),
+++('198765432100100'),
+++('19876543'),
+++('009876543')
)+t+(PhoneNo)|code-block|syntax|javascript|407507|结果：|407508|PhoneNo
-------------
1987654321
1987654321
1987653210
1987654321
1987654321001
19876543
009876543|407509|entityMap^0|8|6|14|6|2V|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

A possible option is the <code>TRIM()</code> function (SQL Server 2017+ is required). By default the <code>TRIM()</code> function removes the space character or other specified characters from the start and end of a string, so you need to put an additional character (<code>#</code> in the example) at the correct positions (1st and 10th) and remove it after that:
<pre><code>SELECT 
 REPLACE(
 TRIM('0' FROM '#' + SUBSTRING(PhoneNo, 1, 10) + '#' + SUBSTRING(PhoneNo, 11, 5)),
 '#',
 ''
 ) AS PhoneNo 
FROM (VALUES
 ('19876543210000'),
 ('198765432100'),
 ('1987653210'),
 ('19876543210'),
 ('198765432100100'),
 ('19876543'),
 ('009876543')
) t (PhoneNo)
</code></pre>
Result:
<pre><code>PhoneNo
-------------
1987654321
1987654321
1987653210
1987654321
1987654321001
19876543
009876543
</code></pre>

blocks|key|407549|text|可以使用字符串函数LEFT+PATINDEX+REVERSE的组合，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|407550|SELECT+PhoneNo,+
+++++++Left(PhoneNo,+
++++++++++++IIF(Len(PhoneNo)-PATINDEX('%25[1-9]%25',Reverse(PhoneNo))%2B1<=10+Or+PhoneNo+Like+'%25[%5E1-9]%25',+
++++++++++++++++10,
++++++++++++++++Len(PhoneNo)-PATINDEX('%25[1-9]%25',+Reverse(PhoneNo))%2B1))+As+NewPhoneNo+
FROM+tblPhone|code-block|syntax|javascript|407551|输出：|407552|PhoneNo++++++++++++++NewPhoneNo
--------------------+--------------------
19876543210000+++++++1987654321
198765432100+++++++++1987654321
1987653210+++++++++++1987653210
19876543210++++++++++1987654321
198765432100100++++++1987654321001
19876543+++++++++++++19876543
009876543++++++++++++009876543|407553|entityMap^0|9|4|E|8|N|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

You can use a combination of string functions <code>LEFT</code> <code>PATINDEX</code> <code>REVERSE</code> as follows:
<pre><code>SELECT PhoneNo, 
 Left(PhoneNo, 
 IIF(Len(PhoneNo)-PATINDEX('%[1-9]%',Reverse(PhoneNo))+1&lt;=10 Or PhoneNo Like '%[^1-9]%', 
 10,
 Len(PhoneNo)-PATINDEX('%[1-9]%', Reverse(PhoneNo))+1)) As NewPhoneNo 
FROM tblPhone
</code></pre>
Output:
<pre><code>PhoneNo NewPhoneNo
-------------------- --------------------
19876543210000 1987654321
198765432100 1987654321
1987653210 1987653210
19876543210 1987654321
198765432100100 1987654321001
19876543 19876543
009876543 009876543
</code></pre>

blocks|key|3752652|text|可以使用CASE表达式返回小于或等于10个字符的值，然后使用REPLACE和RTRIM移除尾随0：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3752653|SELECT+CASE+WHEN+LEN(PhoneNo)+<=+10+THEN+PhoneNo
++++++++++++ELSE+REPLACE(RTRIM(REPLACE(PhoneNo,'0','+')),'+','0')
+++++++END
FROM+(VALUES+('198765432100000'),
+++++++++++++('198765432100'),
+++++++++++++('1987653210'),
+++++++++++++('19876543210'),
+++++++++++++('198765432100100'),
+++++++++++++('19876543'),
+++++++++++++('009876543'))V(PhoneNo);|code-block|syntax|javascript|3752654|entityMap^0|4|4|U|7|12|5|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

You could use a <code>CASE</code> expression to return values with less than or equal to 10 characters as they are, then then <code>REPLACE</code> and <code>RTRIM</code> to remove the trailing 0's:
<pre class="lang-sql prettyprint-override"><code>SELECT CASE WHEN LEN(PhoneNo) &lt;= 10 THEN PhoneNo
 ELSE REPLACE(RTRIM(REPLACE(PhoneNo,'0',' ')),' ','0')
 END
FROM (VALUES ('198765432100000'),
 ('198765432100'),
 ('1987653210'),
 ('19876543210'),
 ('198765432100100'),
 ('19876543'),
 ('009876543'))V(PhoneNo);
</code></pre>

blocks|key|2515203|text|或更短：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2515204|SELECT+IIF(LEN(PhoneNo)+<=+10,+PhoneNo,+REPLACE(RTRIM(REPLACE(PhoneNo,+'0',+'+')),+'+',+'0'))|code-block|syntax|javascript|2515205|这是基于Larnu的回答|2515206|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

or shorter:
<pre><code>SELECT IIF(LEN(PhoneNo) &lt;= 10, PhoneNo, REPLACE(RTRIM(REPLACE(PhoneNo, '0', ' ')), ' ', '0'))
</code></pre>
[This is based on Larnu's answer]

I have an old Phone table which has a PhonoNo column with length 15. Now I am working on a task for phone number cleanup with trailing 0s after 10 digits.
<pre><code>+----------------+---------------+
| PhoneNo | Desire output |
+----------------+---------------+
|198765432100000 | 1987654321 |
| 198765432100 | 1987654321 |
| 1987653210 | 1987653210 |
| 19876543210 | 1987654321 |
|198765432100100 | 1987654321001 |
| 19876543 | 19876543 |
| 009876543 | 009876543 |
+----------------+---------------+
</code></pre>
I am using the below code but it does not work for me.
<pre><code>SELECT PhoneNo, REPLACE(LTRIM(REPLACE(PhoneNo,'0',' ')),' ','0') as NewPhoneNo 
FROM tblPhone WITH(NOLOCK) 
</code></pre>
I am looking for a solution in SQL Server to remove trailing 0s without creating any function.

Remove trailing zeros from phone number

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我有一个旧的电话桌，它有一个长度为15的PhonoNo列。现在我正在做一个电话号码清理的任务，在10位数后的0。+----------------+---------------+|    PhoneNo     | Desire output |+----------------+---------------+|1...

问从电话号码中移除尾随零
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从电话号码中移除尾随零EN