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

John,
	Just add the comma into the accepted characters like
patindex('%[^a-zA-Z0-9, ]%', @s). Also I tested on a string with spaces
and the spaces are left in the return as they are not a part of the
condition.

Query:
 select dbo.F_DropSpecChar('abc fge, 123, NC')

Returns:

abc fge, 123, NC

Jay Hunnicutt

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, October 21, 2009 11:09 AM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] SPAM-LOW: Re: C# replace a special character

I am working on that.  In fact I have already created a stored procedure
- I don't THINK about functions.  I have it working as a SP though.  I
do have to leave characters like ,. and space for my purposes.  This
will be cleaning up name and address fields.  I have tested it though
and it did replace the special character that was giving me fits, but it
also replaced necessary spaces.

How do I add the space character, period and comma to the pattern?

John W. Colby
www.ColbyConsulting.com


Hunnicutt, Jay E. (Civ) wrote:
> John,
> 	If you make it a SQL Scalar function you can use it to strip 
> characters for select, Update and insert statements as needed. Makes 
> it a little easier to use as well as reusable.
> 
> 
> CREATE FUNCTION F_DropSpecChar
> (
> 	-- Add the parameters for the function here
> 	@S varchar(100)
> 
> )
> RETURNS varchar(100)
> AS
> BEGIN
> 	-- Declare the return variable here
> 	DECLARE @Result varchar(100)
> 	DECLARE @i INTEGER
> 
> select @i = patindex('%[^a-zA-Z0-9 ]%', @s)
> 	while @i > 0
> 	begin
> 		select @s = replace(@s, substring(@s, @i, 1), '')
> 		select @i = patindex('%[^a-zA-Z0-9 ]%', @s)
> 	end
> 
> 
> 	SELECT @Result = @S
> 
> 	-- Return the result of the function
> 	RETURN @Result
> 
> END
> GO
> 
> Query:
> select dbo.F_DropSpecChar('qh*(*^&$^1234')
> 
> Returns:
> qh1234
> 
>  
> 
> 
> Jay Hunnicutt
> 
> 
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, October 21, 2009 9:56 AM
> To: Discussion concerning Visual Basic and related programming issues.
> Subject: Re: [dba-VB] SPAM-LOW: Re: C# replace a special character
> 
> Mark,
> 
> This performs an update on an existing column in an existing table 
> simply by specifying the tablename / column name?
> 
> 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
>>
>>
> _______________________________________________
> 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
> 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com