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

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