如何通过 SQL 存储过程替换列值的字符串值 [英] how to replace string values of column values by SQL Stored Procedure

问题描述

我正在使用 SQL Server 2012，我需要从列值中删除特定的字符串数据，该字符串已为用户多次保存到列中.

i am working with SQL Server 2012, i need to remove a particular string data from column values this string has saved multiple times for a user into the column.

我需要为它编写存储过程.我的表结构如下.

I need to write the stored procedure for it. My table structure is like following.

Id      UserId     ColumnNeedUpdate         Address
1       2565       l:\xyz\sfd\mybook.png    Mumbai
1       2565       l:\xyz\sfd\myook.png     Mumbai
1       2565       l:\xyz\sfd\mbook.png     Mumbai
1       2465       l:\xzd\sfd\mybook.png    Mumbai
1       2265       C:\myz\sfd\mybook.png    Mumbai
1       2965       C:\xsz\sfd\mybook.png    Mumbai
1       2565       l:\xyz\sfd\maybook.png   Mumbai
1       2765       C:\zxu\sfd\mybook.png    Mumbai
1       2465       m:\xdz\sfd\mybook.png    Mumbai

现在，如果我为用户 2565 编写选择查询，结果将如下所示.

Now if i write the select query for the user 2565 the result will be following.

1       2565       l:\xyz\sfd\mybook.png    Mumbai
1       2565       l:\xyz\sfd\myook.png     Mumbai
1       2565       l:\xyz\sfd\mbook.png     Mumbai
1       2565       l:\xyz\sfd\maybook.png   Mumbai

编写存储过程的目的是更新列ColumnNeedUpdate"，如下所示.

purpose of writing the stored procedure is to update column "ColumnNeedUpdate" like following.

mybook.png, myook.png,mbook.png,maybook.png

我对 sql server 存储过程很陌生，所以我尝试了以下方法.

I am pretty new in sql server stored procedure so I tried following way.

Create PROCEDURE UsingExistsstoredprocedure(@Id int)
AS
DECLARE @ResultValue int,@ResultFirstName nvarchar(500),@imax int,@i int;
BEGIN TRAN
IF EXISTS(SELECT Name FROM Image WHERE UserId = @Id)
     BEGIN
        SET @imax=@@ROWCOUNT
        SET @i=1

    WHILE(@i<=@imax)
    BEGIN
        SET @ResultFirstName =(SELECT Name FROM [picsilo].[dbo].[Image] WHERE UserId=@i);
        IF CHARINDEX('\',@ResultFirstName)>0
        SET @ResultFirstName=SUBSTRING(@ResultFirstName,0,CHARINDEX('\',@ResultFirstName))
    INSERT Into Image (Name)VALUES(@ResultFirstName)
    END
 END

推荐答案

UPDATE MyTable SET ColumnNeedUpdate = 
    REPLACE(ColumnNeedUpdate, 'l:\xyz\sfd\', '')
WHERE UserId = 2565

免责声明:此代码更改了表中的数据.自行决定使用.

Disclaimer: This code changes the data in your table. Use at your own discretion.

这篇关于如何通过 SQL 存储过程替换列值的字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！