CODEWITHSUNDEEP
sql-serversql-server-2008
pepr
pepr

Reputation: 20760

SQL Server 2008 R2 bug or what?

I am observing a strange behaviour. The following code that is related to conversion of a substring of nvarchar to tinyint works fine:

DECLARE @s nvarchar(50) = N'Line 1'
DECLARE @n tinyint = SUBSTRING(@s, 6, 10)

SELECT
        N'Line 1' AS explicit_nvarchar,
        @s AS nvarchar_variable,
        SUBSTRING(@s, 6, 10) AS nvarchar_substring
        , @n AS tinyint_var_converted_earlier
        , CAST(SUBSTRING(@s, 6, 10) AS tinyint) AS cast_sub_var
        , CAST(SUBSTRING(N'Line 1', 6, 10) AS tinyint) AS cast_nvarchar_substr

I can observe the following output in the Microsoft SQL Server Management Studio:

enter image description here.

However, the code that tries to do with the SELECT from the tables fails if the commented line is uncommented:

Select 
    [order].[identifier] As [order_identifier],
    [plan_data].[starts_on] As [from],
    [plan_data].[ends_on] As [to],
    [workplace].[identifier] AS line_identifier,
    SUBSTRING([workplace].[identifier], 6, 10) AS line_no_str
    --, CAST(SUBSTRING([workplace].[identifier], 6, 10) AS int) AS line_no
From 
    ...

With commented line... enter image description here

but with uncommented line (both for conversion to tinyint or int)...

enter image description here

Update: Strange. I have added the last line to the WHERE to check whether all lines contain the 'Line...

Where
    [plan].[identifier] = @lPlanIdentifier And
    (
        [plan_data].[starts_on] Between @fromUTC And @toUTC Or
        [plan_data].[ends_on] Between @fromUTC And @toUTC)
    AND LEFT([workplace].[identifier], 4) = N'Line'

... and all of a sudden it works also with the uncommented line. How can I discover what causes the problem?

Update2: I should have followed the Hamlet Hakobyan's answer exactly as it was published. When looking inside the table, I can see:

enter image description here

Upvotes: 2

Views: 218

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52645

Whenever you have an error converting a varchar to a number the easiest way to find the data is using IsNumeric. However IsNumeric doesn't mean IsInteger so its worth removing scientific or decimal formats as described by G Mastros in this answer

SELECT
    [order].[identifier] As [order_identifier],
    [plan_data].[starts_on] As [from],
    [plan_data].[ends_on] As [to],
    [workplace].[identifier] AS line_identifier,
    SUBSTRING([workplace].[identifier], 6, 10) AS line_no_str
    --, CAST(SUBSTRING([workplace].[identifier], 6, 10) AS int) AS line_no
FROM
  ...

WHERE 

   IsNumeric(SUBSTRING([workplace].[identifier], 6, 10) + '.0e0') = 0

If you can't fix it but you can use a default value than you could use a case statement to do the conversion only when its legal to do so.

SELECT
    [order].[identifier] As [order_identifier],
    [plan_data].[starts_on] As [from],
    [plan_data].[ends_on] As [to],
    [workplace].[identifier] AS line_identifier,
    CASE WHEN IsNumeric(SUBSTRING([workplace].[identifier], 6, 10) + '.0e0') = 1
             THEN CAST(SUBSTRING([workplace].[identifier], 6, 10) AS int) 
             ELSE Null --Default to null in this example
    END as line_no

Upvotes: 1

bobs
bobs

Reputation: 22184

As indicated by the comments and other answer(s), you have a value in [workplace].[identifier] for one or more rows that fails to cast to an INT. When you added the following to the WHERE clause, you eliminated the row(s) that has this data before it was converted in the SELECT clause:

LEFT([workplace].[identifier], 4) = N'Line'

Run the following to get a distinct list of values in [workplace].[identifier]

SELECT DISTINCT [workplace].[identifier]
FROM [workplace]

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You have value in identifier column for which SUBSTRING([workplace].[identifier], 6, 10) returns ined.

Try this query:

SELECT * FROM [workplace]
WHERE SUBSTRING([identifier], 6, 10) = N'ined'

Upvotes: 4

Related Questions