Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL SUBSTRING vs RIGHT - Best Practice

Tags:

I'm trying to find out the best practice when removing characters from the start of a string.

In some languages, you can use MID without a length parameter however, in TSQL the length is required.

Considering the following code, what is the best practise? (The hex string is variable length)

DECLARE @sHex VARCHAR(66)
SET @sHex = '0x7E260F3DA734AD4BD816B88F485CE501D843DF067C8EDCB8AC7DAD331852E04D'

PRINT RIGHT(@sHex,LEN(@sHex) -2)
PRINT SUBSTRING(@sHex,3,LEN(@sHex) -2)
PRINT SUBSTRING(@sHex,3,65535)
like image 316
John Avatar asked Feb 02 '09 15:02

John


4 Answers

Well, the first is more expressive of your intent. The last is clearly messy (hard-coded length etc). I doubt you'd find much performance difference between the first & second, so I'd use the simplest - RIGHT.

Of course, if you are doing this lots, you could write a udf that encapsulates this - then if you change your mind you only have one place to change...

like image 50
Marc Gravell Avatar answered Sep 22 '22 06:09

Marc Gravell


+1 on the right function, it is much clearer what you want to do

like image 29
SQLMenace Avatar answered Sep 18 '22 06:09

SQLMenace


I would use the right function as it better shows your intent. The substring function is best used for extracting strings from the middle of a longer string.

like image 36
Andrew Hare Avatar answered Sep 22 '22 06:09

Andrew Hare


I use the second SUBSTRING because it is more portable. Not all dbms had LEFT and RIGHT.

like image 45
Yada Avatar answered Sep 22 '22 06:09

Yada