CODEWITHSUNDEEP
sqlsql-serverreplace
Reeya Oberoi
Reeya Oberoi

Reputation: 861

How to update a pattern in a column in SQL Server

I have a table A (CODE, TEXT)

I ran the following query 

select * from A

I see that column TEXT has values with trailing 15 commas

Is it possible to remove these trailing commas by a query?

Upvotes: 0

Views: 1660

Answers (2)

Dnyanesh
Dnyanesh

Reputation: 2343

In case your TEXT column is of type VarChar use 

UPDATE A
SET Text = REPLACE(Text, ',,,,,,,,,,,,,,,', '')
WHERE Text LIKE '%,,,,,,,,,,,,,,,'

In case your TEXT column is of type ntext use

UPDATE A
SET TEXT = CAST(REPLACE(CAST(TEXT as NVarchar(MAX)),',,,,,,,,,,,,,,,','') AS NText)
WHERE Text LIKE '%,,,,,,,,,,,,,,,'

In this case you can not replace directly because if you try to replace it directly it will break with error 'Argument data type ntext is invalid for argument 1 of replace function'

Upvotes: 0

Danny
Danny

Reputation: 1759

If you're really just looking for hard-code "15 commas" to end a field, you could just chop off the last 15 characters of Text:

UPDATE A
    SET Text = SUBSTRING(Text, 1, LEN(Text)-15)
    WHERE Text LIKE '%,,,,,,,,,,,,,,,'

Or you could just replace 15 commas anywhere with empty space anywhere in the Text

UPDATE A
    SET Text = REPLACE(Text, ',,,,,,,,,,,,,,,', '')
    WHERE Text LIKE '%,,,,,,,,,,,,,,,'

Upvotes: 2

Related Questions