Johnny writes "I have come up with a situation that I haven't found an answer to yet[MS SQL 6.5]. If I have a table which contains a field with a value of 'Mc Call-7', I want to reformat the field without spaces, numbers or special characters, so that it is just plain 'McCall'.
This stored procedure works for 1 record, or a small number using a cursor:
DECLARE @person_lastname varchar(30)
DECLARE @maxlength int
DECLARE @value varchar(250)
DECLARE @spacepos integer
SELECT @maxlength = 30
SELECT @value = @person_lastname
WHILE patindex('%[0-9]%', @value) > 0
BEGIN
SELECT @spacepos = patindex("%['-0123456789 ]%", @value)
SELECT @value = CONVERT(CHAR(30),ISNULL(
RTRIM(SUBSTRING(@value, 1, @spacepos-1)) +
LTRIM(SUBSTRING(@value, @spacepos+1, 30 - @spacepos)), @value))
END
This query statement, though complex, works, but only replaces the first occurance and not all unwanted characters:
select
CONVERT(CHAR(30),
ISNULL(RTRIM(SUBSTRING(last_name, 1, patindex("%['-0123456789 ]%", last_name))) +
LTRIM(SUBSTRING(last_name, patindex("%['-0123456789 ]%", last_name), 30 - patindex("%['-0123456789 ]%", last_name))), last_name))
from my_table
HERE'S THE QUESTION:
How can I reformat 'Mc Call-7' into 'McCall' in my_table without using cursors? This is because my_table could contain several 10's - 100's of records to look at, at any one time.
Thanks
Johnny"
Removing special characters from fields 
Posted - 2000-11-02 : 09:04:06