Help with SUBSTRING CHARINDEX

  • I have a field that coonsistently has data in this style: (b) Joe Brown -12563, (a) Mary Edwards -15425 I need to pull out only the name. I have the below which will pull out the name but it won't trim the space, hyphen, and number off. Any ideas?

    SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])) name
  • This works most of the time but if I have data like '(t) Jamie Guy -692' it does not:

    LEFT(SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])),LEN([AGENT])-10)

    • This reply was modified 2 weeks, 1 day ago by  DaveBriCam.
  • LTRIM(RTRIM(SUBSTRING(name, CHARINDEX(')', name) + 1, PATINDEX('%[+-][0-9]%', name) - (CHARINDEX(')', name) + 1))))

    ;WITH test_data AS (
    SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(a) Mary Edwards -15425'), ('(t) Jamie Guy -692'),
    ('(x) Some other person +122') ) AS names(name)
    )
    SELECT *,
    LTRIM(RTRIM(SUBSTRING(name, CHARINDEX(')', name) + 1,
    PATINDEX('%[+-][0-9]%', name) - (CHARINDEX(')', name) + 1))))
    FROM test_data

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I get a "Invalid length parameter passed to the LEFT or SUBSTRING function." error plus what if in the number there were a letter like '-16LK5'?

  • As a thought, are you ALWAYS having that format?  What I mean is an open bracket, a single character, a closing bracking a space, the name, a space a - and a number?

    The reason I ask is if that is ALWAYS the case, you could use something like:

    SELECT LEFT(RIGHT([Agent],LEN([Agent])-4),LEN([Agent])-CHARINDEX('-',[Agent])+4)

    RIGHT is being used to grab all of the characters except the first 4 which gets MOST of the string.  LEFT is then taking that string and grabbing all of the characters until the - character.  With my testing this seems to work.

    IF my assumptions are incorrect, you are going to need to adjust that "- 10" part because that is only valid if the string is a constant length.  To use your query:

    SELECT LEFT(SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])),CHARINDEX('-',[AGENT])-CHARINDEX(')',[AGENT])-2)

    That - 2 at the VERY end of the LEFT is to handle the spaces as there is a space after the ) and a space before the - which we don't need to capture.

    One thing that I would do though is to put all "magic numbers" (the +1 and -2 in your query or the +4 and -4 in my  query) into variables so you can see what they are being used for and why they are needed.  In mine, it would be a "RemoveLeadingChars" (at least that is what I'd call it) and give it a value of 4.  In yours, the 2 would be "RemoveSpaces" and the 1 would be "RemoveSpace".  Not the best names, but I think it works, no?

  • My code works fine with that format of string:

    ;WITH test_data AS (
    SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(a) Mary Edwards -15425'), ('(t) Jamie Guy -692'),
    ('(x) Some other person +122'), ('(y) some other really long name -16LK5') ) AS names(name)
    )
    SELECT *,
    LTRIM(RTRIM(SUBSTRING(name, CHARINDEX(')', name) + 1,
    PATINDEX('%[+-][0-9]%', name) - (CHARINDEX(')', name) + 1))))
    FROM test_data

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • LEFT(SUBSTRING([AGENT],CHARINDEX(')', [AGENT])+1, LEN([AGENT])-CHARINDEX(')', [AGENT])),CHARINDEX('-',[AGENT])-CHARINDEX(')',[AGENT])-2)             -works!

  • It would help if you provided test data and expected results - but this is what I think you are looking for:

    Declare @testTable Table (TestData varchar(100));
    Insert Into @testTable (TestData)
    Values ('(b) Joe Brown -12563')
    , ('(a) Mary Edwards -15425')
    , ('(t) Jamie Guy -692')
    , ('(x)John Q. Public-1023')
    , ('(v) Jane Doe- 4444');

    Select *
    , PersonName = rtrim(ltrim(substring(tt.TestData, p1.pos, p2.pos - p1.pos + 1)))
    From @testTable As tt
    Cross Apply (Values (charindex(')', tt.TestData, 1) + 1)) As p1(pos)
    Cross Apply (Values (charindex('-', tt.TestData, 1) - 1)) As p2(pos);

    If you are on 2017 or higher - you can replace the rtrim/ltrim with the new trim function.  I added a couple of other examples - where the spaces between the data are not the same to show how this works for those differences.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I'm still getting the "Invalid length parameter passed to the LEFT or SUBSTRING function." error.

  • DaveBriCam wrote:

    I'm still getting the "Invalid length parameter passed to the LEFT or SUBSTRING function." error.

    This is why having sample data is important.  The data you have doesn't contain that pattern - either missing the ')' or the '-'.  To fix that - you need to know what is missing and what you want the result to be.  Assuming you are missing the trailing portion - that is, missing the -NNNN portion:

    Declare @testTable Table (TestData varchar(100));
    Insert Into @testTable (TestData)
    Values ('(b) Joe Brown -12563')
    , ('(a) Mary Edwards -15425')
    , ('(t) Jamie Guy -692')
    , ('(x)John Q. Public-1023')
    , ('(v) Jane Doe- 4444')
    , ('(c)Missing Dash');

    Select *
    , PersonName = rtrim(ltrim(substring(tt.TestData, p1.pos, p2.pos - p1.pos + 1)))
    From @testTable As tt
    Cross Apply (Values (concat(tt.TestData, '-'))) As v(TestData)
    Cross Apply (Values (charindex(')', v.TestData, 1) + 1)) As p1(pos)
    Cross Apply (Values (charindex('-', v.TestData, 1) - 1)) As p2(pos);

    This also works if you are missing the '(x)' portion - since it returns position 1 if no ')' is found.  But - if your data contains parenthesis or dashes in other unexpected places then none of the possible solutions would work.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • DaveBriCam wrote:

    I'm still getting the "Invalid length parameter passed to the LEFT or SUBSTRING function." error.

    See the first link in my signature line below and provide the readily consumable format for the data, please.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • All is working now... Cross apply is a great tool

Viewing 12 posts - 1 through 12 (of 12 total)

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