SQL Server - Replace with substring and where clause

Asked By D on 01-Nov-10 01:51 PM
I have a value = '00L103L1' and I want to remove the 'L1' portion at the end.  What is wrong with this query that the result ends up being '0003' rather than my expectation of '00L103'?

update main
set main.volser = replace(main.volser, substring(main.volser,7,2),'')
where clientid = 1 and len(main.volser) = 8 and substring(main.volser,7,2) in ('L1')
undhad ashwin replied to D on 01-Nov-10 02:09 PM
Hi

Try Below Query

update testing set StudentID = (SELECT Replace(StudentID, 'l1', '') from testing where len(StudentID)=8)
where clientid = 1


if any query then reply
D replied to undhad ashwin on 01-Nov-10 02:54 PM

No, the subquery will return more than 1 value when 'clientid = 1' is not in the where clause.  Furthermore, based on the clause I would expect the L1 string to be removed from positions 3+4 and 7+8.  I only want 7+8 removed.

Try this with my original query.  Your query won't make the necessary changes.

create table main1

(clientid numeric(18,0) not null, volser varchar(15) not null)

insert into main1 (clientid, volser) values (1,'000003L1')

insert into main1 (clientid, volser) values (1,'00L103L1')

insert into main1 (clientid, volser) values (1,'00L103')

insert into main1 (clientid, volser) values (2,'000003L1')

insert into main1 (clientid, volser) values (2,'00L103L1')

insert into main1 (clientid, volser) values (2,'00L103')

update main1

set main1.volser = replace(main1.volser, substring(main1.volser,7,2),'')

where clientid = 1 and len(main1.volser) = 8 and substring(main1.volser,7,2) in ('L1')

select * from main1

drop table main1

undhad ashwin replied to D on 02-Nov-10 02:42 PM
Try Below statement

DECLARE @name VARCHAR(50) -- database name
DECLARE db_cursor CURSOR FOR
SELECT volser
FROM main1
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
      update main1 set volser = replace(volser,'L1','') where volser = volser
     FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

select * from main1
D replied to undhad ashwin on 02-Nov-10 02:50 PM
Nope.  Still changes the value '00L103L1' to '0003' instead of '00L103'
Sundarasegaram Veerakuddy replied to D on 03-Nov-10 11:47 AM
Hi,

Could you notice the following in your request:

(a) LEN(Main.Volser) = 8 AND SUBSTRING(Main.Volser,7,2) IN ('L1') - this will list all 8 chr long values that have last 2 chrs as 'L1'
(b) REPLACE(Main.Volser, SUBSTRING(Main.Volser,7,2),'') - this will be simply evaluated to REPLACE(Main.Volser,'L1','') which means replacing all 'L1' with ''.

Naturally you are getting '0003' as the result as both 'L1' are replaced by ''.

REPLACE may not be the proper operation here as it does replace all matching areas with given value. Try using text merging similar to below in order to overcome your problem:

UPDATE <table> SET <column> = LEFT(<column>,LEN(<column>)-2) WHERE <condition>

Note, the column needs to be trimmed when used so that the result will be accurate.
D replied to Sundarasegaram Veerakuddy on 03-Nov-10 11:51 AM
Sundarasegaram,

Yes, using LEFT instead of REPLACE resolves the issue.

Thank you,

Dave
undhad ashwin replied to D on 03-Nov-10 12:05 PM
Hi

Try Below it's done as per your requirement


DECLARE @name VARCHAR(50) -- database name
declare @abc varchar(50)
declare @abc1 varchar(50)
DECLARE db_cursor CURSOR FOR
SELECT clientid,volser
FROM main1
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name ,@abc

WHILE @@FETCH_STATUS = 0  
BEGIN  
print @name
print @abc
set @abc1 = ( select case when len(volser) > 3 then
    case when substring(volser,len(volser)-1,len(volser)) ='l1' then
     substring(volser,0,len(volser)-2)
    else
     volser
    end
    else
       volser
    end as tri
from main1 where  volser = @abc and clientid=@name)

  update main1 set volser =@abc1
from main1 where   volser = @abc and clientid=@name
   FETCH NEXT FROM db_cursor INTO @name  ,@abc
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

select * from main1


D replied to undhad ashwin on 03-Nov-10 12:57 PM
Undhad,

Thanks...I am sure that this would work.  However, simply changing the SET in my original query from using REPLACE to using LEFT works too.

Thanks,

Dave