inconsistent results

Last Post 17 Apr 2008 02:39 PM by alex0603. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
alex0603
New Member
New Member

--
16 Apr 2008 07:02 PM
I run this query in 2000/2005 and they generate differnt results, any ideas?

--F_TABLE_NUMBER_RANGE is a common function on the web that returns a talbe of integers in the range --speified

DECLARE @s VARCHAR(10)
SET @s = ''
SELECT @s= @s+ (SUBSTRING('a142c3d4e5', number, 1)) FROM dbo.F_TABLE_NUMBER_RANGE(1,10)
where SUBSTRING('a142c3d4e5', number, 1) like '%[1-9]%'

ORDER BY number
SELECT @s


Results --
2000- 142345
2005- 5


SwePeso
New Member
New Member

--
16 Apr 2008 11:18 PM
The TVF function F_TABLE_NUMBER_RANGE is located here
http://www.sqlteam.com/forums/topic...C_ID=47685

The TVF function F_TABLE_DATE is located here
http://www.sqlteam.com/forums/topic...C_ID=61519

This is a new "feature" of SQL Server 2005.

A workaoround could look like
DECLARE @s VARCHAR(10)
SET @s = ''

SELECT @s = @s + (SUBSTRING('a142c3d4e5', number, 1))
FROM (
SELECT TOP 100 PERCENT
Number
FROM dbo.F_TABLE_NUMBER_RANGE(1,10)
ORDER BY number
) AS d
where SUBSTRING('a142c3d4e5', number, 1) like '%[1-9]%'

SELECT @s

SwePeso
New Member
New Member

--
16 Apr 2008 11:22 PM
This behaviour is also explained in Books Online.

quote:

SQL Server 2005 sometimes evaluates expressions in queries sooner than when they are evaluated in SQL Server 2000. This behavior provides the following important benefits: The ability to match indexes on computed columns to expressions in a query that are the same as the computed column expression. The prevention of redundant computation of expression results. However, depending on the nature of the query and the data in the database, run-time exceptions may occur in SQL Server 2005 if the query contains an existing unsafe expression. These run-time exceptions include the following: Arithmetic exceptions: zero-divide, overflow, and underflow. Conversion failures such as loss of precision and an attempt to convert a nonnumeric string to a number. Aggregation over a set of values that are not all guaranteed to be non-null. These exceptions may not occur in SQL Server 2000 in a specific application that uses specific data. However, a query-plan that is changed because of changing statistics might potentially lead to an exception in SQL Server 2000. These run-time exceptions can be prevented by modifying the query to include conditional expressions such as NULLIF or CASE. For more information, see Troubleshooting Errors and Warnings on Query Expressions.
alex0603
New Member
New Member

--
17 Apr 2008 02:39 PM
Thank you, I have seen that and I guess I was hopeing for a different answer. I was thinking I could use that as an easier way to remove the charactures. Problem with using the derived table is that I am not assured the order of the result. For instance 1a2b3c may return 132 instead of 123. I had tried -


DECLARE @s VARCHAR(10)
SET @s = ''
SELECT @s = @s + z
from (select top 100 percent SUBSTRING('a1b2c3d4e5', number, 1) as z FROM dbo.F_TABLE_NUMBER_RANGE(1, 10)
where SUBSTRING('a1b2c3d4e5', number, 1) like '%[1-9]%'
ORDER BY number) as d

SELECT @s


But this returns 13524, and I understand why, and it is why I cant use the derived table. I tried your code which is a slight variation or the above and got 135424, again because the derived table will not insure order


Acceptable Use Policy
---