Hi all,

I very much would like to transform the UDF below to be able to 'replace' @output (see below) case-sensitive. This to replace all occurances of 'Ij' into 'IJ'. (Now it alsow replaces 'abcijdef' into 'abcIJdef'.)

I only have no clue where to start asking help about this subject. Any ideas?

Thanks in advance.

====================================================

CREATE FUNCTION vba.FormatCity(
@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
RETURN NULL
END

DECLARE @output varchar(8000)
DECLARE @ctr int, @len int, @found_at int
DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int

SET @ctr = 1
SET @len = LEN(@input)
SET @output = ''
SET @LOWER_CASE_a = 97
SET @LOWER_CASE_z = 122
SET @Delimiter = ' ,-'
SET @UPPER_CASE_A = 65
SET @UPPER_CASE_Z = 90

WHILE @ctr <= @len
BEGIN
WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
SET @ctr = @ctr + 1
END

IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
BEGIN
SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END

SET @ctr = @ctr + 1

WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
BEGIN
IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
BEGIN
SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
END
ELSE
BEGIN
SET @output = @output + SUBSTRING(@input,@ctr,1)
END
SET @ctr = @ctr + 1
END

END

SET @output = REPLACE(@output, ' Ad ',' a/d ')
SET @output = REPLACE(@output, ' A/d ',' a/d ')

SET @output = REPLACE(@output, ' Over ',' over ')

SET @output = REPLACE(@output, ' Aan ',' aan ')
SET @output = REPLACE(@output, ' Den ',' den ')
SET @output = REPLACE(@output, ' Van ',' van ')
SET @output = REPLACE(@output, ' Gem ',' gem ')

SET @output = REPLACE(@output, ' En ',' en ')
SET @output = REPLACE(@output, ' De ',' de ')
SET @output = REPLACE(@output, ' Op ',' op ')
SET @output = REPLACE(@output, ' In ',' in ')
SET @output = REPLACE(@output, ' Bij ',' bij ')

SET @output = REPLACE(@output, 'Ij','IJ') <-- case-sensitive?!

RETURN @output
END

=========================