SQL Substring Maximum Allowable Length?

Posted on 2014-08-13
Last Modified: 2014-08-13
I'm working on an existing stored procedure which uses Substring like so. I need to know what the limit is, 4000?

                             ( ( SELECT Names
                                 FROM    table1
                                 FOR XML path(''), elements), 1, 4000)
Question by:WorknHardr
    LVL 34

    Accepted Solution

    There does not appear to be a maximum as the function can be used with VARCHAR(MAX) and VARBINARY(MAX).
    The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.
    The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2147483647.
    LVL 1

    Expert Comment

    I guess no limit on that.

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now