如何通过 SQL 存储过程替换列值的字符串值

本文介绍了如何通过 SQL 存储过程替换列值的字符串值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用 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.