I have a function that will change the case of the street address. I can get it to change to proper case for the street names but when the street address is 75TH STREET it converts it to 75Th Street. I need it to be '75th Street' . Below is the function I'm using BEGIN DECLARE @Reset bit DECLARE @Ret varchar(512) DECLARE @i int DECLARE @c char(1) SELECT @Reset = 1, @i=1, @Ret = '' WHILE @i <= LEN(@Text) SELECT @c= SUBSTRING(@Text,@i,1), @Ret = @Ret + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END, @Reset= CASE WHEN CASE WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [DOL]''' THEN 1 WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [D][I]' THEN 1 WHEN SUBSTRING(@Text,@i-4,5) like '_[a-z] [M][C]' THEN 1 ELSE 0 END = 1 THEN 1 ELSE CASE WHEN @c like '[a-zA-Z]' or @c in ('''') THEN 0 ELSE 1 END END, @i = @i +1 RETURN @Ret END
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"