Invalid length parameter passed to the substring function.

Posted on 2006-06-01
Last Modified: 2008-02-01
the following query runs fine with the code commented out of the group by, but errors with it - how can I get around it?

select      DayID = convert(numeric,replace(cast(year(EventTime) as varchar(4))+ '/' + right('0' + cast(month(EventTime) as
            varchar(2)), 2) + '/' + right('0' + cast(day(EventTime) as varchar(2)), 2) ,'/','')),
            ImageName = substring(WE.RequestedURI,patindex('%[_]%',WE.RequestedURI)+1, CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI))
--            Requests = count(*)
      from      TDTRANSFER..WebEventsBackup WE with (nolock)
      join URLS with (nolock)
      on      WE.RequestedURI like URL
      where WE.ReferrerURI like '%ene_categories%' or WE.ReferrerURI like '%ene_results%'
      group by convert(numeric,replace(cast(year(EventTime) as varchar(4))+ '/' + right('0' + cast(month(EventTime) as
            varchar(2)), 2) + '/' + right('0' + cast(day(EventTime) as varchar(2)), 2) ,'/','')), CorpCode,
            CustomerNumber, UserID, Application,RequestedURI--, substring(WE.RequestedURI,patindex('%[_]%',WE.RequestedURI)+1, CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI))
Question by:dkocheno
    LVL 7

    Expert Comment

    What error are you getting?  

    You don't have a comma before Requests = count(*).  Also, you should count one of the fields in the Group By clause.  
    Try  ", Request = count(UserID)"


    Author Comment

    Requests are commented out......If I add that I have to use the group by which is why it errors out
    LVL 7

    Accepted Solution

    One of the values in WE.RequestedURI is invalid.  My guess is that it is sending a negative number for this function result:
    CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI)
    LVL 7

    Expert Comment

    To test that, sun this:

    select  RequestedURI
         from     TDTRANSFER..WebEventsBackup WE with (nolock)
         join URLS with (nolock)
         on     WE.RequestedURI like URL
         where WE.ReferrerURI like '%ene_categories%' or WE.ReferrerURI like '%ene_results%'
         AND CHARINDEX('_',WE.RequestedURI, CHARINDEX('_',WE.RequestedURI)+1)-1-patindex('%[_]%',WE.RequestedURI) < 1


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    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.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now