SQL Server - Replace with substring and where clause

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')

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

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

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

Nope.  Still changes the value '00L103L1' to '0003' instead of '00L103'

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.

Sundarasegaram,

Yes, using LEFT instead of REPLACE resolves the issue.

Thank you,

Dave

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