RIGHT function in SQL returns error

Question

I am using SQL Server 2012. I have this statement in my query

SELECT * FROM table1 PC 
JOIN table2 t2 
ON PC.ID = t2.ID
AND 
(
  RIGHT
  (
    PC.C_PRODUCT_ID, 
    (DATALENGTH(PC.C_PRODUCT_ID) - (DATALENGTH(PC.C_PRODUCT_ID) - CHARINDEX(REVERSE('*'), 
    REVERSE(PC.C_PRODUCT_ID)))-1)
  )
) = REPLACE(T2.COMP_PROD_ID,' ','')

and it throws an error

Msg 536, Level 16, State 4, Line 2
Invalid length parameter passed to the RIGHT function.

I took a few examples and see that a positive value is being passed on to the RIGHT function.

An example of C_PRODUCT_ID here in my case

C_PRODUCT_ID = '3-12*LV431536'
Reverse of C_PRODUCT_ID = '635134VL*21-3'

Therefore, according to the above statement

26 – (26- 9) -1
26- 17 -1
9 -1
8

its RIGHT(3-12*LV431536,8) which is LV431536 and this value exists in T2.COMP_PROD_ID.

I hope my question is clear and doesn't deserves to be on hold now.

It results the specified error message if I try your example product ID without a * in it. Are you sure, that all your product IDs contains a * character? — Csaba Benko, Sep 04 '16 at 19:00
@CsabaBenko Yes all of the C_PRODUCT_ID contain a * for sure. — Tanu, Sep 04 '16 at 19:06
Then I think @gbn has a good point below with Unicode strings probably. — Csaba Benko, Sep 04 '16 at 19:16
@CsabaBenko Man, thanks a ton ! there was just one value in the whole table and that was supposed to be used as a placeholder and was causing this error. It works now, thanks !!!!!! — Tanu, Sep 04 '16 at 19:24
No problem. I just ran a couple of times into issues like, "Yes we are sure, all the data is fine". And then it turned out it is not. So that is one of the first things I like to check. :) — Csaba Benko, Sep 05 '16 at 11:45

gbn · Answer 1 · 2016-09-04T19:07:55.837

2

Are you using nvarchar?

If so, DATALENGTH is not the same as LEN

LEN counts characters, excludes trailing spaces
DATALENGTH counts bytes, includes trailing spaces (spoace on disk, basically)

So DATALENGTH will return the double of LEN = RIGHT is broken

As per LEN in the docs

LEN excludes trailing blanks. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters

edited Sep 04 '16 at 19:07

answered Sep 04 '16 at 19:02

gbn

422,506
82
585
676

Please do you think you could take a look at this question : http://stackoverflow.com/questions/40197865/mssql-is-there-any-performance-penalty-for-wrapping-a-select-query-in-a-transa – eddy Oct 22 '16 at 23:38

score 0 · Answer 2 · edited Sep 05 '16 at 05:58

If your problem is a data item without a * an expression like the below one gives you back the whole string in that case as a result:

DECLARE @prodId VARCHAR(255) = '3-12LV431536'
SELECT 
(
    RIGHT
    (
        @prodId, 
        (
            DATALENGTH(@prodId) - 
            (
                DATALENGTH(@prodId) - CASE 
                                    WHEN CHARINDEX(REVERSE('*'), REVERSE(@prodId)) = 0 
                                    THEN DATALENGTH(@prodId) + 1 
                                    ELSE CHARINDEX(REVERSE('*'), REVERSE(@prodId)) END
            )
        -1)
    )
)

RIGHT function in SQL returns error

2 Answers2

Linked