delete "a" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

delete "a"

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-11-13 : 22:14:05

i want to delete the last word from fname when it exists in lname.
problem wit this code is its replacing every single character in the strring with ''. i just want to replace the last word which exists in lname with ''.

declare @table table (fname varchar(20), lname varchar(20))
insert @table
select 'mary a','a' union all
select 'mary', 'a'

select * from @table
select fname,ltrim(right(fname, charindex(' ', reverse(fname)))), lname from @table
where charindex(' ', FNAME) > 0

update @table
set fname = replace(fname, ltrim(right(fname, charindex(' ', reverse(fname)))), '')
where charindex(' ', FNAME) > 0

select * from @table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-13 : 22:28:41

[code]
declare @table table (fname varchar(20), lname varchar(20))

insert @table
select 'mary a','a' union all
select 'mary', 'a'

update t
set fname = left(fname, len(fname) - len(lname))
from @table t
where right(fname, len(lname)) = lname

select * from @table
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-13 : 22:37:43

More complicated one:

declare @table table (fname varchar(20), lname varchar(20))
insert @table 
select 'mary abc pqr','abc' union all
select 'mary', 'a'

select 
	case 
		when charindex(reverse(lname), reverse(fname)) = 1 then rtrim(reverse(substring(reverse(fname),len(lname)+1,len(fname)))) 
		else fname 
	end as fname, lname
from @table

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-13 : 23:20:17

Harsh, your query works ?


declare @table table (fname varchar(20), lname varchar(20))
insert  @table 
select 'mary abc pqr','abc' union all
select 'mary', 'a'

update 	@table
set	fname = replace(stuff(fname, charindex(lname, fname), len(lname), ''), '  ', ' ')

select	* from @table

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-13 : 23:24:46

Yes, it works!

I just posted wrong sample data.

declare @table table (fname varchar(20), lname varchar(20))
insert @table 
select 'mary a','a' union all
select 'mary', 'a'

select 
	case 
		when charindex(reverse(lname), reverse(fname)) = 1 then rtrim(reverse(substring(reverse(fname),len(lname)+1,len(fname)))) 
		else fname 
	end as fname, lname
from @table

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources