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’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.
SELECT SUBSTRING(‘The quick brown fox.‘, 5, 5)
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.
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.
SELECT SUBSTR(‘The quick brown fox.‘, 5, 5) FROM dual;
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.
SELECT SUBSTR(‘The quick brown fox.‘, -3, 3) FROM dual;
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.
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?
2 thoughts on “SQL Server vs Oracle: Substring”
[…] Daniel Janik continues his SQL Server versus Oracle syntax comparison series: […]
I’ve been working with Oracle since version 6 and Sql Server since version 4 (yes the one that still opened the log with ‘copyright Sybase’), and always found that PL-SQL had many cool features: object orientation, approach to pipelined functions, and many other things. No need for Java to do what you want in PL-SQL, while CLR is a must on Sql Server. The big plus of T-SQL, however, is with table variables… hard to say which of the big database giants I prefer!