Modify field contents.  Remove "Atten" from a field that contains "Atten: First Name Last Name"

Posted on 2008-11-03
Last Modified: 2012-05-05
Please help me with a SQL statement that will remove "Atten" from the Attent field that contains rows with:
Jane Doe
Atten Jane Doe
Attention Jan Doe
Jon Doe
Select * from Leas where Attent like 'Atten%'
will return the appropriate rows.  What addtional syntax will remove only the Atten and Attention and leave the names in the Attent field?
Question by:LFreehauf
  • 2
LVL 17

Expert Comment

ID: 22867884
select case
when left(attent,9)='Attention' then right(attent,len(attent)-9)
when left(attent,5)='Atten' then right(attent,len(attent)-5)
else attent
AS TrimmedAttentValue
from Leas

Author Comment

ID: 22868836
I think I need to clarify.
I want to delete any occurrence of "Atten" and "Attention" from the "Attent" field.
Your select statement returns data without the Atten or Attention (Thank You).  However, I want to modify data; remove ("Atten" and "Attention") from the field:
Example of 4 rows and the Attent field prior to modifications:
Jan Doe
Atten Jan Doe
Attention Jon Doe
Jon Doe

Example of the same 4 rows and the Attent field after the modification (desired results):
Jan Doe
Jan Doe
Jon Doe
Jon Doe
LVL 17

Accepted Solution

aflockhart earned 500 total points
ID: 22874566
To modify the original data, use the UPDATE syntax below:  (I have also amended the code to delete the space between "Attention" or "Atten", and "Jane Doe")
Note that this will fail to find occurences where the word "attention" is spelled wrongly; and if there is any punctuation like "Attten : Jane Doe" it may give unwanted results

update leas 
set attent=
select case
when left(attent,9)='Attention' then ltrim(right(attent,len(attent)-9))
when left(attent,5)='Atten' then ltrim(right(attent,len(attent)-5))
else attent 
where left(attent,5)='Atten' 

Open in new window


Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 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