[dba-VB] SPAM-LOW: Re: C# replace a special character

Thanks Mark.

Doing it inside SQL Server is the ideal for sure.  ATM I only have the issue in the Address field of 
the typical FName, LName, Addr, City, St, Zip5, Zip4 record. Thinking about it though you could 
conceivably have this issue in any field.

I will definitely check this out though.

John W. Colby
www.ColbyConsulting.com


Mark Breen wrote:
> Hello John,
> I used the following SQL today, and it works a treat, in my case, I wanted
> to exclude all except alphas, I know that it is not C#, and I know you may
> prefer C# as practice, so if it is no useful, just bin it.
> 
> I got the code from this site, if you have a look, there is a little more
> there that you may be able to make use of.
> 
> Thanks to Nigel Rivett.
> 
> http://www.nigelrivett.net/SQLTsql/RemoveNonNumericCharacters.html
> 
> <snip>
> 
> WHILE @@rowcount > 0
>     UPDATE  tablename
>     SET     columnname = REPLACE(columnname,
>                                     SUBSTRING(columnname,
>                                               PATINDEX('%[^a-zA-Z0-9]%',
>                                                        columnname), 1), '')
>     WHERE   PATINDEX('%[^a-zA-Z0-9]%', columnname) <> 0
> 
> </snip>
> 
> 
> Mark
> 
> 
> 2009/10/20 Charlotte Foust <cfoust at infostatsystems.com>
> 
>> Except the for the semicolon, that's the way to do it in VB.Net too.
>>
>> Charlotte Foust
>>
>> -----Original Message-----
>> From: dba-vb-bounces at databaseadvisors.com
>> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
>> Sent: Tuesday, October 20, 2009 9:22 AM
>> To: dba-vb at databaseadvisors.com
>> Subject: Re: [dba-VB] C# replace a special character
>>
>> Hi John
>>
>> You should be able to use the Replace method directly on your string
>> holding the line or the file:
>>
>>  theStringToCleanUp.Replace(((char)26).ToString(),"");
>>
>> like:
>>
>>  string contents = File.ReadAllText(path);
>>  File.WriteAllText(path, contents.Replace(((char)26).ToString(),""));
>>
>> /gustav
>>
>>
>> _______________________________________________
>> dba-VB mailing list
>> dba-VB at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-vb
>> http://www.databaseadvisors.com
>>
>>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
>