Numeric comparisons on a VARCHAR column.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Numeric comparisons on a VARCHAR column.

Author

Topic

pikthulhu
Starting Member

3 Posts

Posted - 2009-07-02 : 19:28:36

We are dynamically creating tables. Because we don't know what data types will be in a column, we use all VARCHAR columns except for the index (ResID).

However, we want to be able to run something like SELECT * FROM TABLE WHERE Value > 5

Of course, this will require some conversion. This is an example of what I came up with to get the column I will filter on.

select pivottest.ResID, pivottest.Row, pivottest.ColumnName, pivottest.[Value], CONVERT(Decimal(10,4), ij.[Value]) as NumericValue
from pivottest
INNER JOIN
(
select ResID, Row, ColumnName, [Value]
from pivottest
where PATINDEX('%[^0-9.]%', [Value]) < 1
) ij
ON ij.ResID = pivottest.ResID AND ij.ColumnName = pivottest.ColumnName AND pivottest.Row = ij.Row

Now this seems to work just peachy. NumericValue looks to be a decimal with 4 decimal places and everything was converted correctly. I am only getting rows that we will be able to filter on with a numeric comparison like '> 1'

But, when I add that statement:

select pivottest.ResID, pivottest.Row, pivottest.ColumnName, pivottest.[Value], CONVERT(Decimal(10,4), ij.[Value])
from pivottest
INNER JOIN
(
select ResID, Row, ColumnName, [Value]
from pivottest
where PATINDEX('%[^0-9.]%', [Value]) < 1
) ij
ON ij.ResID = pivottest.ResID AND ij.ColumnName = pivottest.ColumnName AND pivottest.Row = ij.Row
WHERE CONVERT(Decimal(10,4), ij.[Value]) > 15.1

I get 'Error converting data type varchar to numeric.'

It seems like maybe the optimizer is trying to flatten the query and run both WHERE clauses at the same time or something, so it is tripping on the non-numeric values that haven't been stripped out yet. Any idea what is going on here?

If there is a better way to do a numeric comparison on a VARCHAR column I would be happy to use that, but I am still very curious as to what is causing my error with this query.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-02 : 19:59:45

There may be a character in the column ij.value that is supposedly numeric.
Does this return anything
SELECT * FROM pivottest where isnumeric(iValue) = 0

There may be a value with a comma in it that would break the conversion
CONVERT(Decimal(10,4), replace(ij.[Value],',','')) would fix that.

Jim

pikthulhu
Starting Member

3 Posts

Posted - 2009-07-02 : 22:35:09

I'll do you one better because I am just testing things on a dummy table for now. Here is the entire table.

ResID Row ColumnName Value
1 0 col1 1
1 0 col2 2
1 0 col3 g3
1 1 col1 11
1 1 col2 22
1 1 col3 33
1 2 col1 maxit
1 2 col2 22.2
1 2 col3 333
2 0 col1 41
2 0 col2 42
2 0 col4 4+3
2 1 col1 411ff
2 1 col2 42G2
2 1 col4 53g3
2 2 col1 511
2 2 col2 4222.2
2 2 col4 4333f

Here is the result of the ij query:
select ResID, Row, ColumnName, [Value]
from pivottest
where PATINDEX('%[^0-9.]%', [Value]) < 1

ResID Row ColumnName Value
1 0 col1 1
1 0 col2 2
1 1 col1 11
1 1 col2 22
1 1 col3 33
1 2 col2 22.2
1 2 col3 333
2 0 col1 41
2 0 col2 42
2 2 col1 511
2 2 col2 4222.2

As you can see, there are no results that cannot be converted to a Decimal. In the very first query I post in the thread, I do exactly that. It is only when I try a cast in the outer WHERE clause that this fails.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 22:49:51

try


select pivottest.ResID, pivottest.Row, pivottest.ColumnName, pivottest.[Value], CONVERT(Decimal(10,4), ij.[Value])
from pivottest
INNER JOIN
(
    select ResID, Row, ColumnName, [Value] = case when PATINDEX('%[^0-9.]%', [Value]) < 1 then [Value] else NULL end
    from pivottest
    where PATINDEX('%[^0-9.]%', [Value]) < 1
) ij
ON ij.ResID = pivottest.ResID AND ij.ColumnName = pivottest.ColumnName AND pivottest.Row = ij.Row
WHERE CONVERT(Decimal(10,4), ij.[Value]) > 15.1

KH
[spoiler]Time is always against us[/spoiler]

pikthulhu
Starting Member

3 Posts

Posted - 2009-07-02 : 23:24:19

Well that worked. Thank you very much. I see what you've changed, but I still don't understand the deep whys behind it.

Is it the case, then, that the outer WHERE is scanning the entire of ij.[Value] then, not just the rows that fit the inner WHERE clause?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 23:29:18

you might be right that the engine flatten the query. By adding the case when . . the outer query will validate against the "case when PATINDEX('%[^0-9.]%', [Value]) < 1 then [Value] else NULL end"

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources