change umlauts per udf

[Di0genes]

Hello,

i need for an export to querry names without german umlauts. I may not use VBA in this case. Therefore I wrote a user-defined function. I would optimize these gladly - if that is possible:

<pre>
CREATE function fn_Umlaute (@strFeld nvarchar(120))
returns nvarchar(120)
as
begin
declare @I int
declare @Ch nvarchar(120)
declare @Ch1 nvarchar(120)
declare @IsUpCase bit
declare @Res nvarchar(120)
declare @geaendert bit

set @I = 1

If isnull(@strFeld,'')<>''
BEGIN
Set @res=''

WHILE @I <= Len(@strFeld)
BEGIN
Set @CH = Substring(@strFeld,@I,1)
set @geaendert = 0
If @I < len(@strFeld) BEGIN Set @CH1 = Substring(@strFeld,@I+1,1) END else BEGIN Set @CH1 = ' ' END -- nächsten Buchstaben laden
IF ASCII(@CH1)=ASCII(UPPER(@CH1)) BEGIN set @isUpCase = 1 END ELSE BEGIN SET @isUpCase = 0 END -- prüfen ob Großbuchstabe

IF ASCII(@CH) = ASCII('Ä') BEGIN
set @Res = @Res + 'A' + CHAR(69*@isUpCase)
set @Geaendert = 1
END
IF ASCII(@CH) = ASCII('Ö') BEGIN
set @Res = @Res + 'O' + CHAR(69*@isUpCase)
set @Geaendert = 1
END
IF ASCII(@CH) = ASCII('Ü') BEGIN
set @Res = @Res + 'U' + CHAR(69*@isUpCase)
set @Geaendert = 1
END
IF ASCII(@CH) = ASCII('ä') BEGIN
set @Res = @Res + 'ae'
set @Geaendert = 1
END
IF ASCII(@CH) = ASCII('ö') BEGIN
set @Res = @Res + 'oe'
set @Geaendert = 1
END
IF ASCII(@CH) = ASCII('ü') BEGIN
set @Res = @Res + 'ue'
set @Geaendert = 1
END
IF ASCII(@CH) = ASCII('ß') BEGIN
set @Res = @Res + 'ss'
set @Geaendert = 1
END

If @geaendert = 0 BEGIN set @Res = @Res + @ch END
set @I = @I+1
END
END
Else
BEGIN
set @Res = ''
END

Return @Res
end
</pre>

Thx for your efforts
regards
Diogenes

 

[Di0genes]

replace

          + CHAR(69*@isUpCase)

by

           + CHAR(69+32*@isUpCase) -- Char(69+0)="E"; Char(69+32)="e"