SQL 替换字符串中的前导字符

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/14216823/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 13:01:41  来源:igfitidea点击:

Replacing leading characters in string

sqlsql-serversql-server-2008

提问by no9

How would i replace first two characters if they are zeros?

如果前两个字符为零,我将如何替换它们?

Example:

例子:

 1. 00001
 2. 00123
 3. 02451

Should be:

应该:

 1. 11001
 2. 11123
 3. 02451

EDIT: Forgot to mention i need this in select clause (in a view) Thanx.

编辑:忘了提到我在选择子句中需要这个(在视图中)谢谢。

回答by Andomar

update  YourTable
set     col1 = '11' + substring(col1, 3, len(col1)-2)
where   col1 like '00%'

In a view, you could do it like:

在一个视图中,你可以这样做:

select   case
         when col1 like '00%' then stuff(col1, 1, 2, '11')
         else col1
         end
from     YourTable;

Live example at SQL Fiddle.

SQL Fiddle 的现场示例。

回答by bummi

declare @a varchar(10)

select @a='01123'

Select case when LEFT(@a,2)='00' then STUFF(@a,1,2,'11') else @a end

回答by DevelopmentIsMyPassion

you can also use left method like below

您也可以使用如下左方法

select case When left(Name,2) = '00' Then stuff(Name, 1, 2, '11')
     else Name
     end
 from YourTable

回答by GamiFlandorfer

SELECT REPLACE(LEFT(MyCol,2),'00','11')+RIGHT(MyCol,3)