chgruver
3/27/2007 2:25:00 PM
On Mar 27, 10:13 am, "EMartinez" <emartinez....@gmail.com> wrote:
> On Mar 27, 9:01 am, chgru...@hotmail.com wrote:
>
>
>
> > Hello, here is my issue, I have one database using MS SQL Server 2000,
> > that has a table with first names, middle names, and last names stored
> > in it along with other information. I have written a stored procedure
> > that when a certain flag in that table gets set the information
> > regarding that person including data from other tables is collected
> > and stored as a record in a temporary table to be sent to another
> > database (this table can at times have thousands of entries).
>
> > The problem is this, the second database for the name fields
> > (firstName, middleName, and lastName) allows only alphabetic
> > characters (A-Z, a-z), spaces, and dashes (-). The database I am
> > getting the data from allows any character.
>
> > My question is this, is there a way to strip out the unwanted
> > characters, replacing them with spaces possibly, inside my stored
> > procedure when I am placing this data into my temporary table? I need
> > to do it within my stored procedure, without the need to download
> > anything, because this stored procedure is the only thing I have the
> > ability to make changes to.
>
> > Chuck
>
> You should be able to use the 'replace' function. Something like this
> should work:
> select replace(FieldName, '&', ' ') from table_x
> Hope this helps.
>
> Regards,
>
> Enrique Martinez
> Sr. Software Consultant
This would work if it were not for multiple different characters that
need to be accounted for the following lines will show unacceptable
characters.
` ~ ! @ # $ % ^ & * ( ) _ = + [ ] { } | \ : ; " ' < > ? , . / 1 2 3 4
5 6 7 8 9 0
The only thing I can assume regarding this would be that I can end up
with a multiple number of these characters in the string, like "Johny
B. Good 5" including the quotes as part of the field.