String Handling Functions - Part 2

In this lesson we're looking at SUBSTRING, the string function used to return a portion of a string from within another string. Do you know what happens if you set the search to start beyond the end of the string? Or before the beginning? It's useful to test a few of the unusual cases so that when you use SUBSTRING you can be confident of the results!

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Rosalind Philips on 10/13/2010

Short and to the point. Will a future session address what to add to your code so that negative values are not included?

Lamar Marsengill on 10/14/2010

The statements: SELECT TOP 10 SUBSTRING(fname, 1, 6) FROM <tablename> SELECT TOP 10 SUBSTRING(fname, 0, 6) FROM <tablename> SELECT TOP 10 SUBSTRING(fname, -1, 6) FROM <tablename> do not return the same results.

A Alagu Ganesh on 10/15/2010

Good

JohnG on 10/15/2010

Full name: Benjamin 0,6 returned: Benja 1,6 returned: Benjam -1,6 returned: Benj

Diana Dee on 10/15/2010

I could not tell from the result, since Aaron has 5 characters, but isn't SUBSTRING (firstname, 0, 6) the same as SUBSTRING (firstname, 1, 5) [i.e. not 6 in the 3rd argument] ?? DrDianaDee@verizon.net

Prathap on 10/17/2010

Very useful

Anthony on 10/22/2010

I know the use of LEN is pretty self explanatory, but I am surprised that it was mentioned and used before being explained. I would have added its usage into part 1.

Don Nesbitt on 11/23/2010

very easy to follow and easy to see substring value

Derek on 2/18/2011

Clear and simple.