Pinku

Pinku

  • 1.4k
  • 308
  • 46.9k

How to insert one column into another column with punctuation removed

Oct 22 2024 11:37 AM

I have one Table called Books which contains  10000 rows and one column Description(this contain books name  ). I have added one new column as newDescription.Now i want to move all the  data from Description column to newDescription column with punctuatin and unnecessary space removed. for example if Description contain "White -()$  Lion" then it should be move to  newDescription column as "White Lion". note: this table contain 10000 data amd all data should be moved based on id.
ex:
update T1
set T1.Description = newDescription --Your Logic Code
from Book T1
INNER JOIN
    Book T2
ON 
    T1.Id= T2.Id;

I have the logic to remove punctuation and white space but dont know how to use it so it will update all row
IF (PATINDEX('%[^ 0-9A-Za-z]%', @RawDescription) > 0)
BEGIN
    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
END
WHILE charindex('  ',@RawDescription  ) > 0
BEGIN
   set @RawDescription = replace(@RawDescription, '  ', ' ')
END


Answers (2)