The SUBSTRING string function returns part of a character, binary, text or image expression. The syntax of the SUBSTRING string function is as follows:
SUBSTRING( <expression>, <start_position>, <length> )
The <expression> parameter is a character string, binary string, text, image, a column or an expression that includes a column. The <start_position> parameter is an integer that specifies where the substring starts and can be of BIGINT data type. The <length> parameter is a positive integer that specifies how many characters or bytes of the <expression> will be returned.
Here are a few uses of the SUBSTRING string function:
Usage #1 : Get the First Name and Last Name from a Full Name
DECLARE @FullName VARCHAR(50) = 'Mark Zuckerberg'
SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS [First Name],
SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [Last Name]
First Name Last Name
------------ ------------
Mark Zuckerberg
Usage #2 : Get the First and Last Word from a String or Sentence
DECLARE @Sentence VARCHAR(MAX) = 'The quick brown fox jumps over the lazy dog'
SELECT SUBSTRING(@Sentence, 1, CHARINDEX(' ', @Sentence) - 1) AS [First Word],
REVERSE(SUBSTRING(REVERSE(@Sentence), 1,
CHARINDEX(' ', REVERSE(@Sentence)) - 1)) AS [Last Word]
First Word Last Word
------------ -----------
The dog
Usage #3 : Get the Domain Name, Page Name and Query Parameter from a URL
DECLARE @URL VARCHAR(1000)
SET @URL = 'http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tid=58'
SELECT SUBSTRING(@URL, 8, CHARINDEX('/', @URL, 9) - 8) AS [Domain Name],
REVERSE(SUBSTRING(REVERSE(@URL), CHARINDEX('?', REVERSE(@URL)) + 1,
CHARINDEX('/', REVERSE(@URL)) - CHARINDEX('?', REVERSE(@URL)) - 1)) AS [Page Name],
SUBSTRING(@URL, CHARINDEX('?', @URL) + 1, LEN(@URL)) AS [Query Parameter]
Domain Name Page Name Query Parameter
-------------------------- -------------------- -----------------
www.sql-server-helper.com tip-of-the-day.aspx tid=58
Usage #4 : Get the User Name and Domain Name from an Email Address
DECLARE @Email VARCHAR(50) = 'helper@sql-server-helper.com'
SELECT SUBSTRING(@Email, 1, CHARINDEX('@', @Email) - 1) AS [User Name],
SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, LEN(@Email)) AS [Domain Name]
User Name Domain Name
----------- -------------
helper sql-server-helper.com
Usage #5 : Convert a Comma-Delimited List to a Table
DECLARE @StringInput VARCHAR(100) = 'Monday,Tuesday,Wednesday'
DECLARE @StringValue VARCHAR(100)
DECLARE @OutputTable TABLE (
[StringValue] VARCHAR(100)
)
WHILE LEN(@StringInput) > 0
BEGIN
SET @StringValue = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))
INSERT INTO @OutputTable ( [StringValue] )
VALUES ( @StringValue )
END
SELECT * FROM @OutputTable
StringValue
-------------
Monday
Tuesday
Wednesday
Usage #6 : Convert a Name/Value Pair List to a Table
DECLARE @NameValuePairs VARCHAR(MAX) = 'Color=White;BackColor=DarkBlue;Font=Arial'
DECLARE @NameValuePair VARCHAR(100)
DECLARE @Name VARCHAR(50)
DECLARE @Value VARCHAR(50)
DECLARE @Property TABLE (
[Name] VARCHAR(50),
[Value] VARCHAR(50)
)
WHILE LEN(@NameValuePairs) > 0
BEGIN
SET @NameValuePair = LEFT(@NameValuePairs,
ISNULL(NULLIF(CHARINDEX(';', @NameValuePairs) - 1, -1),
LEN(@NameValuePairs)))
SET @NameValuePairs = SUBSTRING(@NameValuePairs,
ISNULL(NULLIF(CHARINDEX(';', @NameValuePairs), 0),
LEN(@NameValuePairs)) + 1, LEN(@NameValuePairs))
SET @Name = SUBSTRING(@NameValuePair, 1, CHARINDEX('=', @NameValuePair) - 1)
SET @Value = SUBSTRING(@NameValuePair, CHARINDEX('=', @NameValuePair) + 1, LEN(@NameValuePair))
INSERT INTO @Property ( [Name], [Value] )
VALUES ( @Name, @Value )
END
SELECT * FROM @Property
Name Value
----------- ---
Color White
BackColor DarkBlue
Font Arial