• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

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.
0
danielvic
Asked:
danielvic
1 Solution
 
SirParadoxCommented:
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
Industry Leaders: 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!

 
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

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!

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