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