我正在使用 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 存储过程替换列值的字符串值
发布评论