Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Substring of data prior to a specified character type

    Hi All,

    Can anyone help me with a query I have been trying to get right, I am trying to select a substring of data which should return all data prior to the 3rd hyphen in the value.

    An example value would be JONES-01-CW-NSFT-TEST1 and I would like my substring to return all data before the 3rd hyphen, in this case:

    JONES-01-CW

    But the catch is I need to consider there could be more charcaters before that hypen than the value above, ie:

    THOMPS-011-CWE-NSFT-TEST1 should return THOMPS-011-CWE

    Can anyone help with this as my brain is starting to hurt a little?

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    Quote Originally Posted by roac View Post
    I have been fiddling around with the CHARINDEX function already and I don't seem to be grasping it it appears.

    I have tried various connotations and im not even getting close as I cant get the substring to look at the 3rd hyphen, it only seems to work from the first.

    The basis of my query is below and the field data it returns is the full field minus the first 3 characters...

    How can you specify for it to look from the 3rd hyphen within a substring?

    select substring(field1,
    charindex ('-', field1)-1, LEN(field1))
    FROM table1

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    ROAC pointed you in the direction of your solution.

    If you look at the last example in the link that ROAC pointed you to, it demonstrates how to find the second occurrance of a space.

    You want to find the third occurance of a hypen.

    Second occurance of a space, third occurance of a hypen, sound pretty close to the same logic.

    Look at the example and you'll see the answer.

    Ken
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •