How to check a character in a string and replace that character before inserting

Question

Well, this question includes one part of a complex stored procedure that inserts new entities into multiple tables.

The part that I'm having difficulties with right now needs to work like this:

• add object with original name
• check if the name of the new object contains any special characters listed in Table A “Symbols”
• if so, replace this character with the “replacement character” from table A

EDIT: I got it partially, but still not finished. I still have a problem showing each character replacement combination. Also, in the case of replacing a character that occurs more than once, for example, ".", Substitutions must be performed independently of each other.

ex: # www.test & aol.com → # wwwtest & aol.com, # www.test & aolcom

This is a rough start, I know that parts of this will not work, but I thought it was a worthy starting point:

declare @test varchar(50)
set @test = '#www.test&aol.com'
declare @len int, @ctr int
set @len = LEN(@test)
set @ctr = 1

declare @newName varchar(50)
declare @matchedChar table(match varchar(10),replaceChar varchar(10),processed int         default(0))
declare @alternateEntities table(name varchar(50))
declare @repChar varchar(10)
declare @selectedChar varchar(1)

while @ctr<=@len
begin
--Insert matching characters and replacement characters into table variable, 
--this is necessary for the # character, which has multiple replacement characters
insert into @matchedChar (match,replaceChar) select Character,ReplacementCharacter from     tblTransliterations where Character = SUBSTRING(@test,@ctr,1)

--loop 
while (select COUNT(*) from @matchedChar where processed = 0)>0
begin
    --get the top character from table variable
    set @selectedChar = (select top 1 match from @matchedChar where processed = 0)
    --get replacement character
    set @repChar = (select top 1 replaceChar from @matchedChar where processed = 0)
    --replace character in name string
    --set @newName = (select Replace(@test,@selectedChar,@repChar))     
    set @newName = (select STUFF(@test,CHARINDEX(@selectedChar,@test),1,@repChar))
    --update table variable to move onto next character
    update @matchedChar set processed = 1 where @repChar = replaceChar
    --add name with replaced character to alternate entities table
    insert into @alternateEntities (name) values (@newName)     
end
set @ctr = @ctr+1
set @len = LEN(@test)
end

select * from @alternateEntities

Answer 1

Instead of cycling, use a set-based approach.

• Create a temporary table and fill in the Words column of type NVARCHAR (100), call the temp table Invalid_Words

• Create an Invalid_Words column for each token and make the command col type = bit

• Update temp table bit columns if a word contains a token through a series of update statements

Now you have determined which tokens have been matched for each word.

The next part should replace.