SQL SUBSTRING

There may be reasons why you may only need part of the text and not the whole thing.  For example, if you wanted to organize your customer list by first initial of their company name.  How would you do this?  SQL Server has a function called SUBSTRING().  This function allows for you to specify your starting position and the number of digits you want to take.  If you want the first digit of a customer's company name, you would write your query like this:

SELECT SUBSTRING(CompanyName, 1, 1), *
from CustomerList

You could also use it to find out the Area Code of your customers, by getting the first 3 digits of their phone number:


SELECT SUBSTRING(PhoneNumber, 1, 3), *
from CustomerList


Unlike other programming languages, SQL's SUBSTRING() function is 1-based, while other programming languages are 0-based.  This means that if you want the first digit in SQL, you use 1.  With C#, for example, if you wanted the first digit, you would use 0.

You can also run a query to see how many customers you have for each letter in the alphabet.  This could be done by combining the SUBSTRING() function with a group by, like so:


SELECT SUBSTRING(CompanyName, 1, 1), count(*)
from CustomerList
group by SUBSTRING(CompanyName, 1, 1)
order by SUBSTRING(CompanyName, 1, 1)


SQL SUBSTRING()

What data could you see using the SUBSTRING() function on?