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
sasikala sPosted Dec 11, 2024, 10:17 AM
To achieve the desired results where "Q&A" remains as "Q&A" and "Q & A" is transformed into "Q And A," we need to adjust your SQL code slightly to avoid replacing the ampersand in "Q&A."
Jayraj ChhayaPosted Dec 11, 2024, 10:47 AM
Hi
Check below updated code