How do you strip a character out of a column in SQL Server?

Question

How do you remove a value out of a string in SQL Server?

Answer 1

Use the "REPLACE" string function on the column in question:

UPDATE (yourTable)
SET YourColumn = REPLACE(YourColumn, '*', '')
WHERE (your conditions)

Replace the "*" with the character you want to strip out and specify your WHERE clause to match the rows you want to apply the update to.

Of course, the REPLACE function can also be used - as other answerer have shown - in a SELECT statement - from your question, I assumed you were trying to update a table.

Marc

Answer 2

Take a look at the following function - REPLACE():

select replace(DataColumn, StringToReplace, NewStringValue)

//example to replace the s in test with the number 1
select replace('test', 's', '1')
//yields te1t

http://msdn.microsoft.com/en-us/library/ms186862.aspx

EDIT
If you want to remove a string, simple use the replace function with an empty string as the third parameter like:

select replace(DataColumn, 'StringToRemove', '')

Answer 3

UPDATE [TableName]
SET [ColumnName] = Replace([ColumnName], '[StringToRemove]', '[Replacement]')

In your instance it would be

UPDATE [TableName]
SET [ColumnName] = Replace([ColumnName], '[StringToRemove]', '')

Because there is no replacement (you want to get rid of it).

This will run on every row of the specified table. No need for a WHERE clause unless you want to specify only certain rows.

Answer 4

This is done using the REPLACE function

To strip out "somestring" from "SomeColumn" in "SomeTable" in the SELECT query:

SELECT REPLACE([SomeColumn],'somestring','') AS [SomeColumn]  FROM [SomeTable]

To update the table and strip out "somestring" from "SomeColumn" in "SomeTable"

UPDATE [SomeTable] SET [SomeColumn] = REPLACE([SomeColumn], 'somestring', '')