Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Want to return character(s) after a dash (-)

    Hi Everyone,

    I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.

    It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.

    Any help here will be greatly appreciated.

    Kind Regards,

    David

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @t varchar(50) = '123456-12';
    
    SELECT @t As original_value
         , CharIndex('-', @t) As position_of_hyphen
         , SubString(@t, CharIndex('-', @t) + 1, 50) As right_of_the_hyphen
    ;
    Note that the "50"s (highlighted) are the same value, so that the SubString() function always goes to the end of the string. You can use an arbitrarily large number instead, but this is my personal preference.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    50, instead of 937? I am disappoint.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Rudy doesn't seem to love us anymore.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If I ever implement a magic number, it is [-]937.
    Had to add a surrogate PK to a static table for replication purposes and needless to say it threw people off here when the identity increment was 937 and it didn't start from 1.

    937 will forever be dotted around my code, even if Rudy doesn't love you anymore, Pat
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    declare @nbr1 varchar(25) = '72187-1', @nbr2 varchar(25) = '72187-2'
    select
       nbr1 = substring(@nbr1, charindex('-', @nbr1) + 1, len(@nbr1) - charindex('-', @nbr1))
      ,nbr2 = substring(@nbr2, charindex('-', @nbr2) + 1, len(@nbr2) - charindex('-', @nbr2))
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Greetings rdjabarov! It is great to see you again after ages!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Not sure if I'm sticking around this time either, but - Thanks!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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