Invalid length parameter passed to the LEFT or SUBSTRING function. Trying to remove everything after the period

  • I have a table that has Machinename that looks like the following:

    TH1239.CDB.LOCAL
    TH1238.CDB.LOCAL
    TH1237.CDB.LOCAL
    TH1236.CDB.LOCAL
    TSB1-TLA.CDB.LOCAL
    TSB2-TLA.CDB.LOCAL

    I would like to remove everything after the .

    SO it looks like
    TH1239
    TH1238
    TH1237
    TH1236
    TSB-TLA1
    TSB-TLA1

    This didn't work for me

    SELECT MachineName
          ,LEFT(MachineName, CHARINDEX('.', MachineName) - 1) AS [MCNAME]
    from dbo.FAILED

    gave me this error---- Invalid length parameter passed to the LEFT or SUBSTRING function.

  • RellB - Monday, February 20, 2017 12:52 PM

    I have a table that has Machinename that looks like the following:

    TH1239.CDB.LOCAL
    TH1238.CDB.LOCAL
    TH1237.CDB.LOCAL
    TH1236.CDB.LOCAL
    TSB1-TLA.CDB.LOCAL
    TSB2-TLA.CDB.LOCAL

    I would like to remove everything after the .

    SO it looks like
    TH1239
    TH1238
    TH1237
    TH1236
    TSB-TLA1
    TSB-TLA1

    This didn't work for me

    SELECT MachineName
          ,LEFT(MachineName, CHARINDEX('.', MachineName) - 1) AS [MCNAME]
    from dbo.FAILED

    gave me this error---- Invalid length parameter passed to the LEFT or SUBSTRING function.

    Your data looks different than what's posted to have this error. You can get the error if you have values with trailing spaces, values without a period and probably other things. So you would want to look at your data as that's causing the error. Not sure what data is allowed, what the data type is or what constraints are in place.

    Sue

  • Those specific examples you gave will work fine for that query, however, the problem is that somewhere in your data, you have something that doesn't work.

    You could filter those results out in your where clause (see below), however, I would suggest maybe selecting those results out first, and seeing if they need addressing themselves. This will return your bad rows:
    WHERE CHARINDEX('.', MachineName) = 0

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • RellB - Monday, February 20, 2017 12:52 PM

    I have a table that has Machinename that looks like the following:

    TH1239.CDB.LOCAL
    TH1238.CDB.LOCAL
    TH1237.CDB.LOCAL
    TH1236.CDB.LOCAL
    TSB1-TLA.CDB.LOCAL
    TSB2-TLA.CDB.LOCAL

    I would like to remove everything after the .

    SO it looks like
    TH1239
    TH1238
    TH1237
    TH1236
    TSB-TLA1
    TSB-TLA1

    This didn't work for me

    SELECT MachineName
          ,LEFT(MachineName, CHARINDEX('.', MachineName) - 1) AS [MCNAME]
    from dbo.FAILED

    gave me this error---- Invalid length parameter passed to the LEFT or SUBSTRING function.

    Check for the 0 returned by CHARINDEX when the search character(s) are not found. Convert the 0 to NULL with NULLIF, then to 8000 with ISNULL.
    WITH SomeSampleData AS (
     SELECT *
     FROM (VALUES
     ('TH1239.CDB.LOCAL'),
     ('TH1238.CDB.LOCAL'),
     ('TH1237.CDB.LOCAL'),
     ('TH1236.CDB.LOCAL'),
     ('TSB1-TLA.CDB.LOCAL'),
     ('TSB2-TLA.CDB.LOCAL'),
     ('TSB2-TLA')
     ) d (MachineName)
    )
    SELECT *, LEFT(MachineName,ISNULL(NULLIF(CHARINDEX('.', MachineName),0),8000)-1)
    FROM SomeSampleData


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • My preferred option is to add the character at the end of the string so it won't ever be 0.
    DECLARE @Failed TABLE(
    MachineNAme varchar(100))
    INSERT INTO @Failed
    VALUES
    ('TH1239.CDB.LOCAL '),
    ('TH1238.CDB.LOCAL '),
    ('TH1237.CDB.LOCAL '),
    ('TH1236.CDB.LOCAL '),
    ('TSB1-TLA.CDB.LOCAL'),
    ('TSB2-TLA.CDB.LOCAL'),
    ('TSB-TLA1');

    SELECT MachineName
        ,LEFT(MachineName, CHARINDEX('.', MachineName + '.') - 1) AS [MCNAME]
    from @FAILED

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • use a case statement and don't parse it when the period does not exist.

    SELECT MachineName,
    CASE
    WHEN CHARINDEX('.', MachineName) > 0
    THEN LEFT(MachineName, CHARINDEX('.', MachineName) - 1)
    ELSE MachineName
    END AS [MCNAME]
    from dbo.FAILED

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Worked like a Charm.  Thank you!

  • Just to toss it out there, you could potentially also use PARSENAME here:
    SELECT PARSENAME(MachineNAme, 3)
    FROM dbo.Failed
    WHERE PARSENAME(MachineNAme, 3) IS NOT NULL

  • doug.brown - Tuesday, February 21, 2017 1:11 PM

    Just to toss it out there, you could potentially also use PARSENAME here:
    SELECT PARSENAME(MachineNAme, 3)
    FROM dbo.Failed
    WHERE PARSENAME(MachineNAme, 3) IS NOT NULL

    That query misses rows, that might not be what the OP expects.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Tuesday, February 21, 2017 1:36 PM

    doug.brown - Tuesday, February 21, 2017 1:11 PM

    Just to toss it out there, you could potentially also use PARSENAME here:
    SELECT PARSENAME(MachineNAme, 3)
    FROM dbo.Failed
    WHERE PARSENAME(MachineNAme, 3) IS NOT NULL

    That query misses rows, that might not be what the OP expects.

    The query only misses rows if the OP is looking for all machine names to be returned, whether there is a period in the name or not.  That was not stated in the original requirement. 

    But if you do want all the names, a simple modification gets them:
    SELECT ISNULL(PARSENAME(MachineNAme, 3), MachineNAme)
    FROM dbo.Failed

    I was just pointing out that PARSENAME is yet another way of extracting part of a string before a particular delimiter, especially if the delimiter is a period.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply