I have a script which is accepting "Black AND White" but Idf do like "Black & White" its returning like "Black White" . How to make it work for bot AND and & in sql server. below is the script.
DECLARE @Title varchar(255)='Black AND White' DECLARE @RawDescription varchar(255) =@Title
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 LTRIM(RTRIM(@RawDescription))