remove trailing period

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.
danielvicAsked:
Who is Participating?
 
SirParadoxConnect With a Mentor Commented:
UPDATE company SET Name=CASE WHEN RIGHT(name,1) = '.' THEN LEFT(name, len(name)-1) ELSE Name END
0
 
RaisinJCommented:
UPDATE Company SET Name = Replace(Name,'.','')
0
 
RaisinJCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
danielvicAuthor Commented:
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
 
RaisinJCommented:
Yup, if you only want to get ride of the end period, use SirParadox's solution...
0
 
mastooCommented:
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
 
danielvicAuthor Commented:
SirParadox's solution works.

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

Thanks for all you help!
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.