I am getting CONVERSION error .. please help me understand

image
i have to update col1 where data is > 100 to data/1000

what i am looking for is ( where the data is numeric just update those )

i tried this update ..failing ..

drop create data ...
drop table tempdata 
go 

create table tempdata
(
col1 nvarchar(100)
)
go 


insert into tempdata select  ' PAPER'
insert into tempdata select  'TEA'
insert into tempdata select  '2000'
insert into tempdata select  '  4000'
GO 

select * from tempdata 
go
update giving error ..
UPDATE tempdata 
SET    col1 = col1 / 1000 
WHERE  Isnumeric(col1) = 1 
       AND Cast(col1 AS FLOAT) > 100

image

You cant divide paper by 100. Well you could with a scissor but not in tsql.
Your column data type is varchar and you have a mix of string text and "number" that are not really number but text.

HI yosiasz

What i want is !!!!

to take the data that are numeric in the column
PAPER and TEA do not take
consider 2000 and 4000
notice 4000 has space means ... space4000 .. its nvarchar

out of that data(2000,4000) check if it greater than 100
if it is greater than 100 divide it 1000
and update that value in the table

i want to do this in an update statement

hope this helps
:slight_smile: :slight_smile:

if not lets do brain to brain communication

brain to brain

My brain is not ready for this type of data and I have not had coffee yet.

Maybe use isnumeric to filter the non umeric. But that does not address the underlying design issue why are there mix of data in same column

You should post a sample CREATE TABLE and readily consumable data along with the code you've tried.

The problem here is the fact that SQL Server has to process all of the rows and your CAST is performed prior to the ISNUMERIC.

Why do people insist on using 'real' tables in the 'examples' instead of either #temp or table variables? In fact - for most examples a table variable is much easier to utilize for the example.

Declare @tempdata Table (
        col1 nvarchar(100)
        );

 Insert Into @tempdata (col1)
 Values (' PAPER')
      , ('TEA')
      , ('2000')
      , ('  4000');

 Select *
   From @tempdata t;

 --==== Force isnumeric evaluation first
   With nValues
     As (
 Select ncol = ltrim(rtrim(t.col1))
   From @tempdata t
  Where isnumeric(ltrim(rtrim(t.col1))) = 1
        )
 Select *
      , fcol = iif(nv.ncol > 100, nv.ncol / 1000.0, nv.ncol)
   From nValues nv;

You may not want to use ISNUMERIC though - depends on your actual data and you definitely should not be casting to float. You probably want to cast to decimal or numeric with the correct precision.

Hi Jeff

I used real table here

Normally I prefer
#table

It's a matter of preference

Important for some people
Might not be for others

Many many thanks for your
Help ..
:+1::+1::slightly_smiling_face::slightly_smiling_face:

I guess I hate those little non-obvious arrows, @harishgg1. :wink: Thanks for pointing them out.

Table variables are frequently a PITA because they don't persist and you may need to troubleshoot more than one section of code using common hand-over-hand methods. They also resolve to the optimizer thinking that they only have one row unless your code has an OPTION(RECOMPILE) hint in them. Yes, the new versions of SQL have upped that a bit but it's far from accurate with my usual million row table.

Use TABLE VARIABLEs if you want but I prefer Temp Tables both on the forums and in production.

1 Like

@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

Wow thanks so much Jeff!!! Eye opener!!!!

Heh... except I forgot to include the final solution code after the statement that starts with "So, using those 3 "SQL Oolies","

I've added that it.

And thank you for the kind feedback.

But would it not be better to not have this type of data in the first place and avoid cirque du soleil solutions

Hi Jeff

Thanks a lot
Thank you so much

For explaining it so nicely

:+1::+1::slightly_smiling_face::slightly_smiling_face:

That would be correct and that's the reason for the first sentence in my solution post to @harishgg1, but sometimes one is not in control and data atrocities are heaped upon us that we have to deal with. I've had to deal with a very issue in the past and it was actually because of a good reason. The data was from a columnar based audit table and someone wanted to do reporting on it. The EAV-like audit table is absolutely necessary to save on massive amounts of disk space when they update just 4 columns on thousands of rows of a 137 column table.

And, yea... that 137 column could have been designed a whole lot better but management insists the ROI isn't high enough to justify the rework of everything that touches the table. I'm still working on that "little" problem when I have the time which, unfortunately, is rare.

@harishgg1,

Ok... I was correct in my original thoughts of something like TRY_CONVERT being what you want. I was able to login to a 2016 box this morning and checked it out and it worked. Here's the code to solve your problem:

 UPDATE #tempdata 
    SET col1 = col1 / 1000 
  WHERE TRY_CAST(col1 AS FLOAT) > 100
; 

Notice that you don't need the separate ISNUMERIC or LIKE that we've been using up 'till now, The TRY_CAST does it all very nicely.

Do be advised that TRY_CAST first appeared in SQL Server 2012.

Ah... last but not least, if you only wanted to divide integers by 100 and ignore things that looked like numbers but also have a decimal point, you just need to change FLOAT to INT, like this...

 UPDATE #tempdata 
    SET col1 = col1 / 1000 
  WHERE TRY_CAST(col1 AS INT) > 100
;

It only makes this discrimination if the source is VARCHAR.

For example, the following causes a failure that TRY_CAST can recognize...

 SELECT CAST('4000.00' AS INT)
;

...but the following executes successfully so TRY_CAST wouldn't catch it if the source were some numeric datatype that could contain the number...

 SELECT CAST(4000.00 AS INT)
;

Hi Jeff

Really appreciate your taking time out

And giving the very apt solution

Also appreciate your other thoughts and inputs. They are valuable

Thanks

:+1::+1:

Thank you for the very kind feedback. I got something out of it all, as well... It might make a good example for a couple of "SQL Oolie" articles for SQLServerCentral.com.