Fix line by line parameterization in a table This table has a lot of columns but I need to fix "patrimonio".

It looks like this:



As you can see, there are a lot of different values. The correct way to use this table is with S-1234. But theres a lot of S1234,S 1234,X124 and etc.

I'm trying to update ( dealing with one type of error at a time ) all rows following a certain contidion using stuff.

But this query is returning  The subquery is returning more than 1 value....

begin transaction
            update frotas.Viaturas
            set patrimonio = 
            (select stuff(patrimonio,2,0,'-')
             from   Frotas.Viaturas
             where  patrimonio not like 's-%'
             and    patrimonio not like 's %')
        where patrimonio=patrimonio
        rollback


This is because, is not updating row by row. How can I achieve this ( to update row by row )? I'm trying to think in something with dynamic sql but I can't think in a logic.

The Idea is to update each row with its own value, but with the stuff.
I think you might consider removing all the non-numeric values, then update it with the 'S' hyphen prefix.  Two transactions. I'm operating from the position that you don't really trust the mask/format that was used when inserting the data but the numeric values are okay (e.g. 'As you can see, there are a lot of different values').

Here's a function that removes non-numeric characters (from here: How can I strip non-numeric characters out of a string?)

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) &gt; 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END


Usage: 

UPDATE Viaturas SET patrimonio = dbo.fnRemoveNonNumericCharacters(patrimonio)


Then:

UPDATE Viaturas SET patrimonio = CONCAT('S-', patrimonio)