?
Solved

remove trailing period

Posted on 2004-10-14
7
Medium Priority
?
574 Views
Last Modified: 2012-06-21
I need to be able to remove a trailing period from all values in a column.

Eg.

table company(id, name, address)

some values in the name column are stored with a trailing period, such as 'ACME Inc.' or 'MyCompany Inc.'.

I need to comb through each record and remove the . (period) at the end of the name field.

Any suggestions? This is somewhat urgent, hence the 500 points reward.
Thanks.
0
Comment
Question by:danielvic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Accepted Solution

by:
SirParadox earned 1500 total points
ID: 12310095
UPDATE company SET Name=CASE WHEN RIGHT(name,1) = '.' THEN LEFT(name, len(name)-1) ELSE Name END
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12310117
UPDATE Company SET Name = Replace(Name,'.','')
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12310137
SirParadox's suggestion will work but, it assumes that all the '.' your trying to get ride of are at the end of you value in the "name" column...  The "REPLACE" function just simple replaces a character (anywhere in the string) with another character, in this case, Replace "." with ""...
0
Technology Partners: 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!

 

Author Comment

by:danielvic
ID: 12310139
there could be other periods in the name, but I only want to get the one at the end of the name.

So, I could have A.B.C. and I want to be left with A.B.C

I'll test SirParadox's solution.

thanks.
0
 
LVL 6

Expert Comment

by:RaisinJ
ID: 12310235
Yup, if you only want to get ride of the end period, use SirParadox's solution...
0
 
LVL 21

Expert Comment

by:mastoo
ID: 12310950
Minor and probably irrelevant point but if you want to avoid unnecessary updates:

UPDATE company
  SET Name=LEFT(name, len(name)-1)
  WHERE RIGHT(name,1) = '.'
0
 

Author Comment

by:danielvic
ID: 12311426
SirParadox's solution works.

I had to add an RTRIM on the name otherwise the . was not found.

Thanks for all you help!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

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