-
Update query doubt
I have a table with 2 fields
JOBID PARAMS
FFR054 Test1=1;Test2=2
FFR054 Test1=5;Test2=6
FFR054 Test1=2;Test2=7
Now when i run an update query. I want to remove all the Test1 entries from PARAMS field. The new table structure should be like
JOBID PARAMS
FFR054 Test2=2
FFR054 Test2=6
FFR054 Test2=7
Thanks for any help
-
Try this
update TB1 set jobid = left(jobid,charindex('Test',jobid)-1) from TB1
-
Try this
update TB1
set PARAMS = substring(PARAMS,charindex(';',PARAMS)+1,len(PARAM S)-charindex(';',PARAMS))
-
create table TB1 (JOBID varchar(10),PARAMS varchar(20))
insert into TB1 select 'FFR054', 'Test1=1;Test2=2'
insert into TB1 select 'FFR054', 'Test1=5;Test2=6'
insert into TB1 select 'FFR054', 'Test1=2;Test2=7'
update TB1 set params = right(params,(len(params)-charindex(';',params,1)))
select * from TB1
-
Thanks guys for ur input. But unfortunately, I think I failed to convey my requirement properly.
The table and fields are just an example structure.
I am actually executing the query against a SQL Server from a VB.NET code.
JOBID PARAMS
FFR054 Param1=1;Param2=2;Param3=5
FFR054 Param1=5;Param2=6;Param3=6
FFR054 Param1=2;Param2=7;Param3=7
I will only know, Which Parameter to be removed at the RUN TIME OF THE CODE.
So for example, if Param3 is to be removed. I would like only Param1 and Param2 alone in the tables. So is the case.
It does not matter to have a Semicolon ( at the end like
Param1=1;Param2=2;
But two semicolons should not be there continuously as in (this might happen if Param2 is to be removed)
Param1=1;;Param3=3
Pls let me know, if this is possible through a SQL query, if not I sd manipulate it through VB code.
Thanks for any help
-
Might be more than one parameter got removed ?
-
You can write a stored procedure which takes a parameter to indicate which parameter to be removed, then write three different update statements to chop off the parameter.
-
I havent optimized it, but this works
declare @params varchar(50)
--pass 'param1=' or 'param2=' or 'param3='
set @params = 'param1='
--case1
update TB1 set params= case charindex(';',params,charindex(@params,params,1) )
when 0 then substring(params, charindex(@params,params,1), (len(params)-charindex(@params,params,1))+1)
else substring(params, charindex(@params,params,1),charindex(';',params,c harindex(@params,params,1) ) -charindex(@params,params,1))
end from tb1
-
OOPS. I have to do a inverse of the above sql statement I gave. Here you go
--truncate table tb1
--insert into tb1 select 'FFR054','Param1=1;Param2=2;Param3=5'
--insert into tb1 select 'FFR054','Param1=5;Param2=6;Param3=6'
--insert into tb1 select 'FFR054','Param1=2;Param2=7;Param3=7'
declare @params varchar(50)
--pass 'param1=' or 'param2=' or 'param3='
set @params = 'param3='
--case1
update TB1 set params= case charindex(';',params,charindex(@params,params,1) )
when 0 then replace(params,substring(params, charindex(';'+@params,params,1),(len(params)-charindex(';'+@params,params,1))+1) ,'')
else replace(replace(params,substring(params, charindex(@params,params,1), 1+charindex(';',params,charindex(@params,params,1) ) -charindex(@params,params,1)),''),';;','')
end from tb1
-
Thanks MAK for your response. I got it. I used the ur second method and modified it a little to suit my output.
This is the query that I have now to do that
UPDATE dbo.TABLE
SET PARAMS =
CASE CHARINDEX('PARAM=1', PARAMS)
WHEN 0 THEN PARAMS
ELSE
REPLACE(PARAMS,SUBSTRING(PARAMS, CHARINDEX ('PARAM=1', PARAMS),
(CHARINDEX(';', PARAMS, CHARINDEX('PARAM=1', PARAMS)) - CHARINDEX('PARAM=1', PARAMS)) + 1), '')
END
Thanks for your help again.
-
Oops I posted the query wrongly.. here is the right one. I changed the PARAM=1 in the previous to PARAM1= Where PARAM1 is the Selected Parameter to be deleted.
This is the updated query
UPDATE dbo.TABLE
SET PARAMS =
CASE CHARINDEX('PARAM1=', PARAMS)
WHEN 0 THEN PARAMS
ELSE
REPLACE(PARAMS,SUBSTRING(PARAMS, CHARINDEX ('PARAM1=', PARAMS),
(CHARINDEX(';', PARAMS, CHARINDEX('PARAM1=', PARAMS)) - CHARINDEX('PARAM1=', PARAMS)) + 1), '')
END
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|