3

Given:

  • SQL Server
  • Table called TEST_TABLE
  • Column in TEST_TABLE called TEST_FIELD of type VARCHAR(50) NOT NULL
  • Row 1: 10YR3/6
  • Row 2: 10YR3/2
  • Query: SELECT TEST_FIELD FROM TEST_TABLE WHERE ...

Question:

In my where condition I need to test for values in the last character of the string. I notice the same behavior doing the following in the Where clause.

  1. RIGHT(TEST_FIELD,1) > 3
  2. CAST(RIGHT(TEST_FIELD,1) AS INT) > 3

Are they behaving the same through some inferred cast in case 1? Is case 1 deterministic?

Thanks in advance.

Matt

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matt
  • 1,441
  • 1
  • 15
  • 29

2 Answers2

2

A conversion is done when you check the value for instance:

DECLARE @t varchar(100)

SET @t = (SELECT 'ABCA2')

SELECT @t    

IF RIGHT(@t, 2) > 10
  SELECT 'Hi'
ELSE
  SELECT 'Bye'

Will throw an error because SQL cannot convert A2 to an integer without throwing an error.

However if you replace @t with:

SET @t = (SELECT 'ABC12')

The above code will work as a conversion is successful and a comparison can be made. The right function itself does not convert your value. MSDN states the return type of RIGHT() explicitly:

Returns varchar when character_expression is a non-Unicode character data type.

Returns nvarchar when character_expression is a Unicode character data type.

To make it easier on yourself, eliminate the function RIGHT() altogether, when a comparison is done with text for instance:

DECLARE @t varchar(100)

SET @t = (SELECT '1')

SELECT @t

IF @t < 10
  SELECT 'Hi'
ELSE
  SELECT 'Bye'

Notice I did not make a call to Right(). The result of the above is the display of 1 and then of the text Hi.

JonH
  • 32,732
  • 12
  • 87
  • 145
  • So in my case I have several unique values in the value itself. 10YR3/6. 10, 3, and 6. Not utilizing right would mash that together wouldn't it? 1036? or worse 10 instead of 3 or 6? Not sure on that behavior so my question continues with this in mind. – Matt Dec 22 '11 at 16:45
  • @MattAkers - If you can parse the values to get 10, 3, and 6 respectively then you are ok. If you can't then the comparison of your text with an integer datatype will throw an exception. You cannot compare 10YR3/6 with a value of say 100. It doesn't know how to perform the operation, and this has nothing to do with Right() or even Left() it is more to do with the conversion when the comparison is done. Does this make sense? – JonH Dec 22 '11 at 16:49
  • That makes perfect sense. I completely agree with your examples and comments. My question more over when and where a comparison conversion will pass/fail is: why do where clause 1 and 2 perform identically and if 1 appears to be having a cast done in the black box, do I really need to cast it outright? And does where clause 1 have deterministic behavior? – Matt Dec 22 '11 at 17:07
  • 1
    `and if 1 appears to be having a cast done in the black box, do I really need to cast it outright?` No you do not have to cast it. `And does where clause 1 have deterministic behavior?` Yes – Ben English Dec 22 '11 at 17:33
0

And Yes case 1 is deterministic - either successful implicit conversion from string to integer(end then comparison between two integers) or exception thrown.

bako_mite
  • 24
  • 3