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
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
3
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
0
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
0
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 %
#1
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
3
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
0
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
0
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 %