SQL Server vs Oracle: Substring

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?

2 thoughts on “SQL Server vs Oracle: Substring

  1. 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!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s