Pinku

Pinku

  • 1.4k
  • 292
  • 43.7k

How to keep apostrophe while removing special character in string

Oct 23 2024 2:04 PM

I have a code which is removing special character and unnecessery spaces from the string. but it also removing apostrophe which Is not correct.
 

    declare @RawDescription as nvarchar(255)='sustainability /* ener*#@gys'
    WHILE PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription) > 0
    BEGIN
        IF SUBSTRING(@RawDescription, PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription), 1) = '-'
        BEGIN
            SET @RawDescription = REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription), 1), ' ')
        END
        ELSE
        BEGIN
            SET @RawDescription = REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription), 1), '')
        END
    END

    WHILE CHARINDEX('  ', @RawDescription) > 0
    BEGIN
        SET @RawDescription = REPLACE(@RawDescription, '  ', ' ')
    END

    select LTRIM(RTRIM(@RawDescription))

The above could should working as it is like removing special character and spaces but it should keep apostrophe . for ex if the string is : sustainabilitie's /* ener*#@gie's  then it should be like : sustainabilitie's  energie's


Answers (1)