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