After removing the spaces, you can use the STUFF() to inject a decimal point into the proper position before converting to NUMERIC .

To inject the decimal point relative to the end of the string, calculate the inject position as an offset from LEN(string) . Alternately, the string can be reversed, the decimal point injected after the first two digits, and reversed again.

To guard against short values such as "9" representing "0.09", padding the input value with leading zeros may be needed.

SELECT *
FROM InputData I
CROSS APPLY (SELECT REPLACE(I.FullPrice, ' ', '') AS NoSpaces) NS
CROSS APPLY (SELECT '00' + NS.NoSpaces AS Padded) P
CROSS APPLY (SELECT STUFF(P.Padded, LEN(P.Padded) - 1, 0, '.') AS Decimal) D
--CROSS APPLY (SELECT REVERSE(STUFF(REVERSE(P.Padded), 3, 0, '.')) AS Decimal) D
CROSS APPLY (SELECT CONVERT(DECIMAL(6,2), D.Decimal) AS Result) R

The above builds up the conversion step-by-step, using CROSS APPLY s to perform the calculations. For actual use, these calculations can be combined as follows. In this case, the reverse-stuff-reverse technique is used, because it eliminated the duplicate expression needed by the LEN() function.

SELECT
    *,
    CONVERT(DECIMAL(6,2),
        REVERSE(
            STUFF(
                REVERSE(
                    '000' + REPLACE(I.FullPrice, ' ', '')
                ),
            3, 0, '.')
        )
    ) AS Result
FROM InputData I

Results:

See this db<>fiddle .

A more simplistic approach would be to convert to a larger type like DECIMAL(8,2) and divide the converted result by 100.

Declare @sampleData as table
(FullPrice  nvarchar(max))

insert into @sampleData(FullPrice)
values('1819 55')

insert into @sampleData(FullPrice)
values('1875 123')

UPDATE @sampleData
SET FullPrice = REPLACE(FullPrice, ' ', '')

Select FullPrice AS FullPrice_s, CASE WHEN Isnumeric(FullPrice) = 1
THEN convert(decimal(18,2),FullPrice)
ELSE 0 END AS FullPrice
From @sampleData

Select FullPrice AS FullPrice_s, CASE WHEN Isnumeric(FullPrice) = 1
THEN convert(decimal(6,2),FullPrice)
ELSE 0 END AS FullPrice
From @sampleData

Actual issue is convert(decimal(6,2),FullPrice) , the reason is the number of digits is more than 6 that's the reason you are getting Arithmetic overflow error converting nvarchar to data type numeric error.

My steps would be following:

1. Create a new temporary column TempFullPrice for the target datatype (Decimal(18,2))
2. Populate this temporary column with values from FullPrice , after performing some convert operations
3. Copy back the data from TempFullPrice to FullPrice
4. Update the datatype of FullPrice to Decimal(18,2)
5. Drop the temporary column

Queries:

Alter table Table1 add TempFullPrice decimal(18,2)

update Table1 set TempFullPrice= convert(decimal(18,2) ,replace(FullPrice,' ',''))/100

update Table1 set FullPrice=TempFullPrice

alter table Table1 alter column FullPrice decimal(18,2)

alter table Table1 drop column TempFullPrice