Remove a single character from a varchar field SQL

问题:

I have a table with several varchar columns that are almost identical to primary keys I have in another table, with the exception of a period (.). I've looked at the replace function in T-SQL but the first argument isn't an expression. How can I remove all occurrences of a particular character with SQL? It seems like the correct answer might be to replace with a zero length string. Is that close?

To whomever felt the question didn't exhibit research effort it was mainly due to a misunderstanding of the documentation itself.

回答1:

You can update the table directly using REPLACE on the column values:

UPDATE myTable
SET myColumn = REPLACE(myColumn, '.', '')

回答2:

Do you want to remove all instances of the . from the string? If so, you were right about REPLACE:

DECLARE @Example TABLE
(
    Value VARCHAR(100)
)
INSERT @Example (Value)
VALUES ('Test.Value'), ('An.otherT.est')

SELECT
    REPLACE(Value, '.', '')
FROM
    @Example

-- Replace only the first '.'
SELECT
    STUFF(Value, CHARINDEX('.', Value, 0), 1, '')
FROM
    @Example

Edit, making the example a little more useful since I played around with it anyway, I might as well post the example. :)

回答3:

update your_table
set some_column = replace(some_column, '.', '')