How do I use substring in SQL to eliminate characters from the right?

I have a column named "name", I want to update name
from this:
John Q. Smith<this is what I want to remove>
to this:
John Q. Smith
I tried to use "reverse" with substring but it did not work.  
Jack SeamanAsked:
Who is Participating?
 
chapmandewCommented:
declare @x varchar(100)
set @x = 'John Q. Smith<this is what I want to remove>'

select left(@x, charindex('<', @x)-1)

so..

update tablename
set fieldname = left(fieldname , charindex('<', fieldname )-1)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
John Q. Smith
to this:
John Q. Smith -------------- ????????????????????? seems like a copy paste error  /
0
 
Faiga DiegelSr Database EngineerCommented:
you want to remove trailing spaces after the word "Smith"??? Like RTRIM(ColumnNameHere)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Jack SeamanAuthor Commented:
I want to remove the actual text after the name.  There are no trailing spaces.
0
 
chapmandewCommented:
you just need to be able to identify where the string starts after the name that you want to remove
0
 
HainKurtSr. System AnalystCommented:
try this

update Users
set [name]=replace([name],'<this is what I want to remove>','')
where charindex('<this is what I want to remove>', [name])>0
0
 
sameer2010Commented:
Looks like he wants to remove everything beyond Smith and then update name. If this is so, you need to specify if there is a specific length or specific delimiter that decides the data that needs to be kept. For your example, just UPDATE SET NAME = 'John Q. Smith' would work.
If it is length based delimiting, then use

SET NAME = select substring(NAME,1,8).

If it is after first occurence of some delimiter, say <, then you can code the following

SET NAME = substring(NAME,1,case when patindex('%<%',NAME)> 0 then patindex('%<%',NAME)-1 else len(NAME) end)
0
 
Jack SeamanAuthor Commented:
Will
update tablename
set fieldname = left(fieldname , charindex('<', fieldname )-1)

work for multiple rows of data.  I have a table with >4000 rows that I have to edit the data field?
0
 
chapmandewCommented:
it will work for as many records as you want, but you need to find a common way to know where the name ends for the records....does that make sense?
0
 
Jack SeamanAuthor Commented:
What I want to remove always starts with < and always ends with >
0
 
chapmandewCommented:
then the code I gave you will work just fine.  
0
 
sameer2010Commented:
Then use the query i posted with similar modification for >
0
 
sameer2010Commented:
Use this
declare @a varchar(16);
declare @pattern varchar(5);
set @pattern='%<%>%';
set @a='abcdfef<hij>this';
select substring(@a,1,case when patindex(@pattern,@a) > 0 then patindex(@pattern,@a)-1 else len(@a) end) +
 substring(@a,case when patindex(@pattern,@a) > 0 then patindex('%>%',@a)+1 else len(@a) end,
case when patindex(@pattern,@a)>0 then len(@a) - patindex('%>%',@a) else 0 end);

Open in new window

0
 
Jack SeamanAuthor Commented:
chapmandew
Your code worked fine.  Had to modify a little to account for nulls:
update tablename
set fieldname = left(fieldname , charindex('<', fieldname )-1)
where fieldname is not null

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.