StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
3220824
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2010-07-10T21:02:31.430
FavoriteCount
0
LastActivityDate
2010-07-11T19:56:55.410
LastEditDate
2010-07-11T19:56:55.410
LastEditorUserId
135152
OwnerUserId
135152
ParentId
3220584
PostTypeId
2
Score
13
ViewCount
0
LastEditorDisplayName
text
Body
<p>This is one of those examples of how there's similar functionality between SQL and the various extensions, but are just different enough that you can not guarantee portability between all databases.</p> <p>The <a href="http://oreilly.com/catalog/sqlnut/chapter/ch04.html" rel="noreferrer">SUBSTRING keyword, using PostgreSQL syntax (no mention of pattern matching) is ANSI-99</a>. Why this took them so long, I dunno...</p> <p>The crux of your need is to obtain a substring of the existing column value, so you need to know what the database substring function(s) are called.</p> <h2>Oracle</h2> <hr> <pre><code>SELECT SUBSTR('abcd_01', -2) FROM DUAL </code></pre> <p>Oracle doesn't have a RIGHT function, with is really just a wrapper for the substring function anyway. But <a href="http://techonthenet.com/oracle/functions/substr.php" rel="noreferrer">Oracle's SUBSTR</a> does allow you to specify a negative number in order to process the string in reverse (end towards the start).</p> <h2>SQL Server</h2> <hr> <p>Two options - <a href="http://msdn.microsoft.com/en-us/library/ms187748.aspx" rel="noreferrer">SUBSTRING</a>, and <a href="http://msdn.microsoft.com/en-us/library/ms177532.aspx" rel="noreferrer">RIGHT</a>:</p> <pre><code>SELECT SUBSTRING('abcd_01', LEN('abcd_01') - 1, 2) SELECT RIGHT('abcd_01', 2) </code></pre> <p>For brevity, RIGHT is ideal. But for portability, SUBSTRING is a better choice...</p> <h2>MySQL</h2> <hr> <p>Like SQL Server, three options - <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substr" rel="noreferrer">SUBSTR</a>, <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring" rel="noreferrer">SUBSTRING</a>, and <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_right" rel="noreferrer">RIGHT</a>:</p> <pre><code>SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2) SELECT SUBSTRING('abcd_01', LENGTH('abcd_01') - 1, 2) SELECT RIGHT('abcd_01', 2) </code></pre> <h2>PostgreSQL</h2> <hr> <p>PostgreSQL only has <a href="http://www.postgresql.org/docs/current/static/functions-string.html" rel="noreferrer">SUBSTRING</a>:</p> <pre><code> SELECT SUBSTRING('abcd_01' FROM LENGTH('abcd_01')-1 for 2) </code></pre> <p>...but it does support limited pattern matching, which you can see is not supported elsewhere.</p> <h2>SQLite</h2> <hr> <p>SQLite only supports <a href="http://www.sqlite.org/lang_corefunc.html" rel="noreferrer">SUBSTR</a>:</p> <pre><code>SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2) </code></pre> <h2>Conclusion</h2> <hr> <p>Use RIGHT if it's available, while SUBSTR/SUBSTRING would be better if there's a need to port the query to other databases so it's explicit to others what is happening and should be easier to find equivalent functionality.</p>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POquery for substring formation
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USOMG Ponies
UserOwnerUserId
1. USOMG Ponies
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POquery for substring formation
  singulars
  PostTypePostTypeId
  PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
2. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
3. VO
  singulars
  PostPostId
  PO
  UserUserId
  This table or related slice is empty.
  VoteTypeVoteTypeId
  VTUpMod
CommentsPostId
1. COUpdated to mention ANSI-99 SUBSTRING...
  singulars
  PostPostId
  PO
  UserUserId
  USOMG Ponies

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.