Home Forums SQL Server 2008 T-SQL (SS2K8) Invalid length parameter passed to the LEFT or SUBSTRING function. Trying to remove everything after the period RE: Invalid length parameter passed to the LEFT or SUBSTRING function. Trying to remove everything after the period

  • February 20, 2017 at 1:33 pm

    #1929434

    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]