?
Solved

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

Posted on 2010-01-05
14
Medium Priority
?
183 Views
Last Modified: 2012-05-08
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.  
0
Comment
Question by:Jack Seaman
  • 4
  • 4
  • 3
  • +3
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26181499
John Q. Smith
to this:
John Q. Smith -------------- ????????????????????? seems like a copy paste error  /
0
 
LVL 15

Expert Comment

by:Faiga Diegel
ID: 26181592
you want to remove trailing spaces after the word "Smith"??? Like RTRIM(ColumnNameHere)
0
 

Author Comment

by:Jack Seaman
ID: 26181636
I want to remove the actual text after the name.  There are no trailing spaces.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 26181637
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 26181644
you just need to be able to identify where the string starts after the name that you want to remove
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26181702
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
 
LVL 13

Expert Comment

by:sameer2010
ID: 26181729
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
 

Author Comment

by:Jack Seaman
ID: 26181747
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 26181756
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
 

Author Comment

by:Jack Seaman
ID: 26181817
What I want to remove always starts with < and always ends with >
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26181841
then the code I gave you will work just fine.  
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26181846
Then use the query i posted with similar modification for >
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26181995
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
 

Author Closing Comment

by:Jack Seaman
ID: 31672981
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question