SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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?

Confessions of a Microsoft Addict

Daniel Janik has been supporting SQL Server for 18 years as a DBA, developer, architect, and consultant. He spent six years at Microsoft Corporation supporting SQL Server as a Senior Premier Field Engineer (PFE) where he supported over 287 different clients with both reactive and proactive database needs. Daniel has spoken at several SQL Saturday events across the US and Caribbean and regularly speaks at PASS local chapters.

Comments

Leave a comment on the original post [sqltechblog.com, opens in a new window]

Loading comments...