Ben's Blog

If for no one else, I wanted to post this somewhere for my own reference.  Having used SQL server during my “formative years” I find myself writing queries in Oracle and cursing aloud when my old ways of doing things don’t work.  Here is one that I can’t seem to commit to memory.

If I wanted to grab the left portion of some data in SQL Server I would do the following:
select LEFT(theField,10) from theTable

If I wanted to grab the left portion of the data that preceded some character like the ‘@’ in an email address I would do something like the following:
select LEFT(theField,charIndex(theField,’@')-1)

For reference, here are the Oracle Equivalents:

To grab the left 10 characters:
select SUBSTR(theField,1,10)

To grab the right 10 characters:
select SUBSTR(theField,-10)

To grab the characters to the left of the ‘@’ sign like above:
select SUBSTR(theField,1,instr(theField,’@')-1)

This entry was posted on Monday, March 29th, 2010, 10:39 am and is filed under Tech Notes. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.