If you're using SQL2000, then you should
If your using SQL2000, you may want to convert this into a function to make
it a little more reusable (I modified the script to allow for ASCII
character 32 (' ')):

CREATE FUNCTION dbo.strip 
(
	@Expression	varchar(4000)
)  
RETURNS VARCHAR(4000)
AS  
BEGIN 
DECLARE @position int, @fixedstring varchar(4000)
-- Initialize the variables.
SET @position = 1
SET @fixedstring = LOWER(@Expression)

WHILE @position <= DATALENGTH(@fixedstring)
BEGIN
   IF ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 1))) BETWEEN 33 
AND 47) OR ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 1))) BETWEEN 
58 AND 64) OR ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 1))) 
BETWEEN 91 AND 96) OR ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 
1))) BETWEEN 123 AND 126)
   BEGIN
	SET @fixedstring = STUFF(@fixedstring, @position, 1, '')
   END
   ELSE
   BEGIN   
	SET @position = @position + 1
   END
END

RETURN @fixedstring

END



=================================
Kyle M. Burns, MCSD, MCT
ECommerce Technology Manager
Centra Credit Union
kburns@c...

 


-----Original Message-----
From: Jet Ski [mailto:quikshift@a...]
Sent: Thursday, November 01, 2001 2:07 PM
To: sql language
Subject: [sql_language] Re: Case Elimination Script


Gentlemen, 

Thank you for the leads. This is the prototype code that I came up with to 
perform the operation. Seems to run very quickly and do the job. If anyone 
has any performance improvements or comment, shout them out.

Thank you for the help.
-j



SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position 
-- and for the character string.
DECLARE @position int, @string varchar(30), @fixedstring varchar(30)
-- Initialize the variables.
SET @position = 1
SET @string = 'L@o...%an Simons-son'
SET @fixedstring = @string

WHILE @position <= DATALENGTH(@fixedstring)
BEGIN
   IF ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 1))) BETWEEN 32 
AND 47) OR ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 1))) BETWEEN 
58 AND 64) OR ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 1))) 
BETWEEN 91 AND 96) OR ((SELECT ASCII(SUBSTRING(@fixedstring, @position, 
1))) BETWEEN 123 AND 126)
   BEGIN
	SET @fixedstring = STUFF(@fixedstring, @position, 1, '')
   END
   ELSE
   BEGIN   
	SET @position = @position + 1
   END
END
SELECT @string as ORIGINAL, @fixedstring as STRIPPED
GO