Pinku

Pinku

  • 1.4k
  • 308
  • 46.2k

How to replace replace special character but not & in SQL SERVER

Dec 11 2024 9:49 AM

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

 


Answers (2)