我的字符串是这样的:
1 ,QCIM1J25836, QCIM1J27637
2 ,QCIM1J25836, QCIM1J27637, QCIM1J27638
我想仅删除第一个逗号,这意味着我的输出将是为
1 QCIM1J25836, QCIM1J27637
2 QCIM1J25836, QCIM1J27637, QCIM1J27638
也许在其他地方就不会有逗号...所以请告诉我如何更新所有类似的数据...
我的字符串是这样的:
1 ,QCIM1J25836, QCIM1J27637
2 ,QCIM1J25836, QCIM1J27637, QCIM1J27638
1 QCIM1J25836, QCIM1J27637
2 QCIM1J25836, QCIM1J27637, QCIM1J27638
您可以使用STUFF
函数:
SELECT col1,
STUFF(col2,1,1,'') as [Col Without First Comma]
FROM tbl
WHERE col2 LIKE ',%'
或者您可以使用 RIGHT
SELECT col1,
RIGHT(col2,LEN(col2)-1) as [Col Without First Comma]
FROM tbl
WHERE col2 LIKE ',%';
或者您可以使用 SUBSTRING
函数。
SELECT col1,
SUBSTRING(col2, 2, 255) as [Col Without First Comma]
FROM tbl
WHERE col2 LIKE ',%';
更新
根据您的评论,您也可以使用相同的方法进行更新:
使用SUBSTRING
函数
UPDATE tbl
SET col2 = SUBSTRING(col2, 2, 255)
WHERE col2 LIKE ',%';
RIGHT
函数。UPDATE tbl
SET col2 = RIGHT(col2,LEN(col2)-1)
WHERE col2 LIKE ',%';
STUFF
。UPDATE tbl
SET col2 = STUFF(col2,1,1,'')
WHERE col2 LIKE ',%';
UPDATE table
SET col2 = CASE WHEN LEFT(col2 ,1) =',' THEN RIGHT(col2,LEN(col2)-1)
ELSE col2 END
UPDATE table
SET col2 = case when charindex(',',col2,0) =1 then right(col2, len(col2)-1) else col2 end
USE [LIB]
CREATE FUNCTION [dbo].[trimChar]
(
@p_string varchar(max),
@p_char varchar(1)
)
RETURNS varchar(max)
AS
BEGIN
declare @l_string varchar(max) = @p_string
-- lets do the front
while SUBSTRING(@l_string,1,1) = @p_char
begin
set @l_string = substring(@l_string,2,len(@l_string))
end
-- lets do the back
set @l_string = reverse(@l_string)
while SUBSTRING(@l_string,1,1) = @p_char
begin
set @l_string = substring(@l_string,2,len(@l_string))
end
set @l_string = reverse(@l_string)
return @l_string
END
GO