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