I have a function which is returning 'Q&A' as QA and 'Q & A ' as ' Q And A' but what i need is like 'Q&A' as Q&A and 'Q & A ' as 'Q And A'. Below is the code.
DECLARE @RawDescription varchar(255) ='Q&A' SET @RawDescription = REPLACE(@RawDescription, ' & ', ' And ') WHILE PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription) > 0 BEGIN IF SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1) = '-' BEGIN SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), ' ')) END ELSE BEGIN SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), '')) END END WHILE CHARINDEX(' ', @RawDescription) > 0 BEGIN SET @RawDescription = REPLACE(@RawDescription, ' ', ' ') END
select @RawDescription