How to remove Punctuation from SQL Query

Question

Here is the query i have written . If input is 'sustainab/ility-en,ergy' then iit should always be populated with the value of TItleDescription with punctuation removed or hyphen replaced by space . so the result should be  'sustainability energy'
But the below query giving me result like 'sustainabilityenergy' How to fix it.

DECLARE @TitleDescription varchar(255) ='sustainab/ility-en,ergy'
DECLARE @RawDescription varchar(255) = @TitleDescription
--'sustainab/ility - en,ergy'
IF (PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription) > 0)
BEGIN
   WHILE (PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription) > 0)
   BEGIN
       SET @RawDescription = TRIM(REPLACE(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription), 1), ''), ' ', ' '))
   END
END

SELECT @TitleDescription, @RawDescription

Muhammad Imran Ansari · Answer

Hi Pinku The issue in your query arises because you are removing all non-alphanumeric characters which leads to joining the words together without spaces. Try the below query It should work. DECLARE @TitleDescription varchar(255) = 'sustainab/ility-energy' DECLARE @RawDescription varchar(255) = @TitleDescription -- Remove specific characters like '/' '' without adding spaces and replace '-' with a space IF (PATINDEX('%[^ 0-9A-Za-z]%' @RawDescription) > 0) BEGIN WHILE (PATINDEX('%[^ 0-9A-Za-z]%' @RawDescription) > 0) BEGIN -- Handle '/' and '' by removing them handle '-' by replacing with space 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 END -- Final output SELECT @TitleDescription @RawDescription Thank you.