Continuing the comparison between these two database giants, we dive into the substring function. If you’ve been working with databases for a while, I’m sure you’ve had to parse a string and while you’d think these are the same they work a little different and I think Oracle may surprise you a bit.
SQL Server
SQL Server’s substring function is really straight forward and works just like you’d think. Pass in a string or column, tell it where to start, give it a number of characters you want to return.
Example:
SELECT SUBSTRING(‘The quick brown fox.‘, 5, 5)
Result:
quick
Substring isn’t it though; SQL Server has a few tricks up it’s sleeve, LEFT() & RIGHT(). These two functions take either the left N number of characters or the right N number of characters, giving more options for would be SQL coders.
Oracle
If you’re coming from a SQL Server and have worked with SUBSTRING quire a bit then you may feel you’ve already mastered the SUBSTR() function in Oracle; but, you haven’t.
Example:
SELECT SUBSTR(‘The quick brown fox.‘, 5, 5) FROM dual;
Result:
quick
Looks the same as SQL Server, right? Well, Oracle is has something cool up it’s sleeve just as well. Here’s something that SQL Server can’t do with SUBSTRING.
Example:
SELECT SUBSTR(‘The quick brown fox.‘, -3, 3) FROM dual;
Result:
fox
That’s right, Oracle’s SUBSTR function can take a negative value. The only catch is that you can’t go past the length of the string. In this case the length of this string is 20. So if you did –20, the result would be nothing. –19 would start at the T in The. Hopefully that didn’t confuse you too much. My recommendation would be to download Oracle and give it a try.
Conclusion
Parsing strings is a feature that is often needed in the database world and SUBSTRING/SUBSTR are designed to do just that. I find it interesting how these two platforms approached the functions differently and that’s definitely shows how you can do many things to get to the same answer.
I honestly can’t say which I prefer. What do you all think?
