用% SQL server替换第一个或最后一个字符。

I have string:

我有字符串:

*rg*niza*io* 

I want to replace % in the first and last character of the string. Desired out put is:

我要在字符串的第一个和最后一个字符中替换%。想要将是:

%rg*niza*io%

4 个解决方案

#1

If you want to replace only * with % from first and last positions. Then,

如果您只想用%从第一和最后的位置替换*。然后,

Query

查询

SELECT CASE
        WHEN LEFT([string_column], 1) = '*' AND  RIGHT([string_column], 1) = '*' 
        THEN '%' + SUBSTRING([string_column], 2, LEN([string_column]) - 2) + '%'  
        WHEN LEFT([string_column], 1) = '*' AND RIGHT([string_column], 1) <> '*' 
        THEN '%' + RIGHT([string_column], LEN([string_column]) - 1)
        WHEN LEFT([string_column], 1) <> '*' AND RIGHT([string_column], 1) = '*' 
        THEN LEFT([string_column], LEN([string_column]) - 1) + '%'
        ELSE [string_column] END AS [updated_string_column]
FROM [your_table_name];

Demo

#2

I'm just answering because the obvious to me is:

我只是回答，因为对我来说显而易见的是:

select '%' + substring(str, 2, len(str) - 2) + '%'

Of course, this would be a bit more complicated if you want to conditionally replace the characters when they are '*'.

当然，如果您想在“*”时有条件地替换字符，这将会更复杂一些。

#3

Use STUFF,LEFT and LEN string functions

使用东西，左和LEN字符串函数

Declare @string varchar(50) = '*rg*niza*io*'

select stuff(left(@string,len(@string)-1),1,1,'%')+'%'

Result : %rg*niza*io%

结果:% rg * niza * io %

#4

Use a combination of the CONCAT, LEFT, SUBSTRING & LEFT functions.

使用CONCAT、LEFT、SUBSTRING和LEFT函数的组合。

SELECT CONCAT('%',LEFT(SUBSTRING(yourfield,2,LEN(yourfield)),LEN(yourfield)-2),'%')
FROM yourtable

Output: %rg*niza*io%

输出:% rg * niza * io %