Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

How to recursively replace text within a column for a

I have a table [tbl_Customer] where I have a column that contains a customer's name [Customer_Name].  It contains first and last names together with some middle initials.  

I'd like to be able to run an update where I can remove the middle initial of certain letters.  

The select statement below, for example, returns a list of customers where I want to remove the middle initial E:

SELECT     Customer_Name
FROM         tbl_Customer WHERE     (Customer_Name LIKE '% E %')

Returns:

Jane E Public
John E Public

When the replace is complete, I would like the name to remove the selected initial and the table would contain the following for the two entries shown above:

Jane Public
John Public

Thanks, in advance, for your help.
0
robbid
Asked:
robbid
1 Solution
 
Scott PletcherSenior DBACommented:
DECLARE @save_updated_values TABLE (
    Customer_Id INT,
    Customer_Name nvarchar(100)
    )

UPDATE tbl_Customer
SET
    Customer_Name = REPLACE(Customer_Name, ' E ', '')
OUTPUT
    INSERTED.Customer_Id, INSERTED.Customer_Name INTO @save_updated_values
WHERE
    Customer_Name LIKE '% E %'

SELECT *
FROM @save_updated_values
ORDER BY
    Customer_Name
0
 
robbidAuthor Commented:
I was also able to do the same with this code:


UPDATE tbl_Customer
SET Customer_Name = REPLACE(Customer_Name,' W ',' ')
WHERE Customer_Name LIKE '% W %'

Open in new window

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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now