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