Remove excessive spaces and numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Remove excessive spaces and numbers

Author

Topic

gostros
Starting Member

6 Posts

Posted - 2011-03-24 : 12:00:45

There's a field in my data called 'Customer Name'. Some of these customer names contain the name, then spaces, then a number. I want to remove all excessive spaces from the data. Then for all the records that end in a number, I want to remove ONLY the number at the end of the field. Here's some examples:

Joe's A/C
Joe's A/C (several spaces) 501
Joe's A/C (several spaces) 208
HVAC Store

In the end, I want to have cleaner data, no excessive spaces and no customer names should end in a number.

Any help is appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 12:04:52

What about "Joe's A/C 501A"? How should that be handled?

gostros
Starting Member

6 Posts

Posted - 2011-03-24 : 12:08:02

quote:
Originally posted by robvolk

What about "Joe's A/C 501A"? How should that be handled?

Then leave the numbers. Only remove numbers if they are at the end of the Customer Name. All Customer Names should end in a letter.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 12:21:45

select reverse(substring(reverse(CustomerName), PATINDEX('%[^0-9 ]%', reverse(CustomerName)), Len(CustomerName)))
from myTable

gostros
Starting Member

6 Posts

Posted - 2011-03-24 : 14:41:56

quote:
Originally posted by robvolk

select reverse(substring(reverse(CustomerName), PATINDEX('%[^0-9 ]%', reverse(CustomerName)), Len(CustomerName)))
from myTable

awesome, thanks for the quick reply. This particular db is in MS Access. What needs to be changed to get it to work in Access?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-24 : 14:49:24

quote:
What needs to be changed to get it to work in Access?

The forum that you asked the question in!

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 14:53:46

Well, posting in the MS Access forums in the first place would be the first change.

AFAIK Access doesn't have a PATINDEX function, nor does it have REVERSE, but you can use InStrRev to find strings from the end instead of the beginning. InStr is the equivalent of SUBSTRING. Without a wildcard matching function you'll have to trim trailing digits one at a time.

It's probably best to write your own digit-trimming function in VB and call it from your Access query.

gostros
Starting Member

6 Posts

Posted - 2011-03-24 : 15:16:49

I did some testing in SQL Server in another db and it works well, thanks. I did notice that in some cases, the customer name ends in a symbol, like '.' or '+'. How could I remove those as well?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 15:29:46

select reverse(substring(reverse(CustomerName), PATINDEX('%[A-Z]%', reverse(CustomerName)), Len(CustomerName)))
from myTable

Give that a try.

gostros
Starting Member

6 Posts

Posted - 2011-03-24 : 17:38:36

Many thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources