This table has a lot of columns but I need to fix "patrimonio".

It looks like this:

enter image description here

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

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.


3 Answers 3


Not an SQL Server user but why the sub-select? Why not:

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

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))
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    RETURN @strText


UPDATE Viaturas SET patrimonio = dbo.fnRemoveNonNumericCharacters(patrimonio)


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

I too think stripping the non-numeric characters are the way to go. Instead of a function you can do it in a single Update statement, assuming your patterns are well defined in your example. This example will strip the non-numeric, make sure you have a 4 character number, and concatenate it to the end of S-

--create table frotas.Viaturas(id int, patrimonio nvarchar(50));

--insert frotas.Viaturas
--  (742,'S-0667'),
--  (743,'S-0668'),
--  (744,'S-0916'),
--  (745,'S-0917'),
--  (746,'S0811'),
--  (747,'S765')

UPDATE frotas.Viaturas
SET patrimonio = 
        WHEN LEN(REPLACE(STUFF(patrimonio, PATINDEX('%[^0-9]%', patrimonio), 1, ''),'-','')) < 4 THEN N'S-'+RIGHT(N'0000'+REPLACE(STUFF(patrimonio, PATINDEX('%[^0-9]%', patrimonio), 1, ''),'-',''),4)
        ELSE N'S-'+REPLACE(STUFF(patrimonio, PATINDEX('%[^0-9]%', patrimonio), 1, ''),'-','')
FROM frotas.Viaturas;

SELECT * FROM frotas.Viaturas;
  • I like the all-in-one approach, but this code doesn't strip all non-numeric values. I think you would be hard-pressed to find a readable UPDATE approach to do this. For example: stackoverflow.com/a/17950550/426622
    – John
    Commented Jun 28, 2016 at 16:19
  • In the truest sense of stripping non-numerics, you are correct. But based on the OP's patterns, we don't need to over engineer the solution. If you truly need to do that, then your function approach would be better. And if you already have the function or will need one in the future, go for it. But for this, right now, this singlular set-based approach will do trick. Commented Jun 28, 2016 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.