SUBSTRING String Function¶

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.

Sample Uses of the SUBSTRING String Function¶

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.database-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.database-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@database-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      database-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