How to insert one column into another column with punctuation removed

Pinku

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty1y ago

Accepted answer

Create a SQL function to remove punctuation and unnecessary spaces.

CREATE FUNCTION dbo.fn_RemovePunctuationAndSpaces (@RawDescription NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
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

    WHILE CHARINDEX('  ', @RawDescription) > 0
    BEGIN
        SET @RawDescription = REPLACE(@RawDescription, '  ', ' ')
    END

    RETURN LTRIM(RTRIM(@RawDescription))
END

Now you can write an UPDATE query that uses this function to clean the Description column and move the data to newDescription.

UPDATE T1
SET T1.newDescription = dbo.fn_RemovePunctuationAndSpaces(T1.Description)
FROM Book T1
INNER JOIN Book T2
ON T1.Id = T2.Id;

Brahma Prakash Shukla1y ago

To move all the data from the Description column to the newDescription column with punctuation removed and unnecessary spaces condensed, you can apply your logic to remove non-alphanumeric characters and then update the newDescription field for each row.

We can modify your existing logic to be used in an UPDATE query that will handle all rows. Here's how you can write it:

-- Update the newDescription column with cleaned data from the Description column
UPDATE Book
SET newDescription = LTRIM(RTRIM(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(
                                                REPLACE(
                                                    REPLACE(
                                                        REPLACE(
                                                            REPLACE(
                                                                Description,
                                                            '-', ' '),      -- Replace hyphen with space
                                                        '(', ''),           -- Remove left parenthesis
                                                    ')', ''),              -- Remove right parenthesis
                                                '$', ''),                -- Remove dollar sign
                                            '.', ''),                  -- Remove period
                                        ',', ''),                    -- Remove comma
                                    '!', ''),                    -- Remove exclamation
                                '?', ''),                    -- Remove question mark
                            ':', ''),                    -- Remove colon
                        ';', ''),                    -- Remove semicolon
                    '''', ''),                    -- Remove single quote
                '"', '')                     -- Remove double quote
    ))
WHERE Description IS NOT NULL;

-- Optional step to remove any extra spaces created by the replacements
UPDATE Book
SET newDescription = LTRIM(RTRIM(REPLACE(newDescription, '  ', ' ')))
WHERE CHARINDEX('  ', newDescription) > 0;