@harishgg1,
You probably already know that mixing datatypes in a column in the form of a VARCHAR or even SQL_VARIANT is usually one of the worst things you can do to data so I'll skip that lecture. On to the problem.
I don't know for sure because I'm not on an SQL Server Instance that has it just now but you might be able to use the likes of TRY_PARSE or TRY_CONVERT to solve this issue.
The code that Jeff Williams (@jeffw8713) posted does the trick but it doesn't update and also requires extra decisions.
With that, I'll say we can solve this using a handful of T-SQL "Oolies".
The first Oolie is that a lot of people don't know that you can update a CTE and it'll update the underlying table.
The second Oolie that a lot of people don't know is that a CTE and all the code it serves is evaluated and treated as a single piece of code rather than the CTE being evaluated as a separate entity. That means that the following code looks like it should work but will fail because it's evaluated as surely as if it were written as a single UPDATE:
WITH Cte AS
(
SELECT col1
FROM dbo.tempdata
WHERE col1 NOT LIKE '%[^ 0-9]%'
)
UPDATE x
SET col1 = col1/1000
FROM Cte x
WHERE col1 > 100
;
Of course, that still resorts in the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ' PAPER' to data type int.
Most people give up at that point and go to something much more complex. But there's a third Oolie that a whole lot of people don't know about. You CAN force the Cte to materialize as a result set just as surely as if it were a physical table. You just need to invoke a "blocking operation" that will cause the Execution Plan to materialize the data in a pre-filtered fashion just as if the Cte were a table with nothing but valid divisible data. That is done using the near zero cost TOP filter even though we're using a TOP(value) that will return all rows.
So, using those 3 "SQL Oolies", the following very simple code will work as expected on the given test data.
WITH Cte AS
(
SELECT TOP (2147483647)
col1
FROM dbo.tempdata
WHERE col1 NOT LIKE '%[^ 0-9]%'
)
UPDATE x
SET col1 = col1/1000
FROM Cte x
WHERE col1 > 100
;
Ah... you've probably noticed that I also did NOT use ISNUMERIC. That's because ISNUMERIC is actually another Oolie that people don't know about or think that it's broken. ISNUMERIC must NEVER be used as an "IsAllDigits" function because there are numeric values that contain things other than what you might expect. For example, the following will cause every non-digit value to still return ISNUMERIC values of 1 simply because they can be converted to SOME datatype.
--===== ISNUMERIC RETURNS a "1" for all of these
SELECT ISNUMERIC(',')
,ISNUMERIC(',,,,,,')
,ISNUMERIC('8E3')
,ISNUMERIC('8D3')
,ISNUMERIC('¤')
,ISNUMERIC(CHAR(9))
,ISNUMERIC(CHAR(10))
,ISNUMERIC(CHAR(11))
,ISNUMERIC(CHAR(12))
,ISNUMERIC(CHAR(13))
;
Even though a "space" character doesn't return a 1 for ISNUMERIC, leading and trailing spaces are ignored during implicit mathematical conversions and that's why the space was included in the pattern that I used for the LIKE.
For more information on ISNUMERIC and why it shouldn't ever be used as an "IsAllDigits" function, please see the following article.
https://www.sqlservercentral.com/articles/why-doesn%e2%80%99t-isnumeric-work-correctly-sql-spackle
Thanks for pointing them out.