Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

cleaning out non numbers from a field


I have a varchar that is supposed to be a phone number, the field
sometimes contains things other than numbers.  I would like to clean
this up for a report I have to generate and was hoping there is some
way to do this without using a bunch of nested replace() statements,
also I don't have the authority to go in and change the data base
(which is what should probably be done).  Any thoughts?
On 6 Mar, 19:26, "Jeffrey42" <jjohnso@gmail.com> wrote:

> I have a varchar that is supposed to be a phone number, the field
> sometimes contains things other than numbers.  I would like to clean
> this up for a report I have to generate and was hoping there is some
> way to do this without using a bunch of nested replace() statements,
> also I don't have the authority to go in and change the data base
> (which is what should probably be done).  Any thoughts?

well...

The ISNUMERIC() function should quickly help you strip out your
offending/non offending candidates. Then while it's not a performance
god, you could loop between i=0 and 26 and replace CHAR(i+ 65) (I
think it's 65, you'll need to check).

However the question which you will need to clarify is what do the
other characters mean? if they are entered in error, was it in place
of a digit, in which case is your data reliable. If they were entered
to mean something then surely that meaning needs to be translated?

-----------------------------------------------Reply-----------------------------------------------

> well...

> The ISNUMERIC() function should quickly help you strip out your
> offending/non offending candidates. Then while it's not a performance
> god, you could loop between i=0 and 26 and replace CHAR(i+ 65) (I
> think it's 65, you'll need to check).

> However the question which you will need to clarify is what do the
> other characters mean? if they are entered in error, was it in place
> of a digit, in which case is your data reliable. If they were entered
> to mean something then surely that meaning needs to be translated?

For the most part the extra char are either - between numbers or ext.
or just space.  The others I have seen are F and M, I am sure this was
supposed to go in the sex field so I am not worried about the
integrity of the data for the vast majority.

Thanks for the help

-----------------------------------------------Reply-----------------------------------------------

On 6 Mar, 20:03, "Jeffrey42" <jjohnso@gmail.com> wrote:

It sounds like this is one for minds far greater than mine. In my
opinion SQL server is not the place to be altering strings, the
functions are not particularly flexible, and such manipulation does
not lend itself to a declarative approach. I've seen some really
clever work arounds to strip out or insert particular characters,
however the problem you've encountered is that you need to perform a
lot of operations on the same field. This either lends itself to a
complex UDF, or a load of replace as you originally stated. I assume
you are outputting the data to some kind of reporting tool, it is
possible that there's more flexible behaviour in there for applying
regular expressions or similar. Of course I'm only familiar with 2000,
hopefully someone will say "ah, but there's a REGEX function in
2005!", but I wouldn't know.

All the best with your problem,
Someone.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc