Solved

remove trailing period

Posted on 2004-10-14
7
566 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
7 Comments
 
LVL 1

Accepted Solution

by:
SirParadox earned 500 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Query: need to find records in table_a with no matches in table_b 14 30
Sql Query Datatype 2 19
Sql Query 4 19
Need SQL that flips  value 5 21
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now