none
need string part after second hyphen?

    Question

  • Hello,

    I having string in below format,

    DECLARE @p_name VARCHAR(100)
    SET @p_name = '356-877-TEST'
    SET @p_name = '67356-87H7-TEST24'

    now I need string part after second hyphen, please suggest.

    output,

    TEST

    TEST24

    Tuesday, November 26, 2013 10:33 AM

Answers

All replies

  • Try the below:(Assuming , you have only maximum 3 "-" in your value.)

    DECLARE @p_name VARCHAR(100)
    --SET @p_name = '356-877-TEST'
    SET @p_name = '67356-87H7-TEST24'
    
    Select PARSENAME(REPLACE(@p_name,'-','.'),1)



    Tuesday, November 26, 2013 10:36 AM
  • Simplest is to REVERSE the string and find the first hyphen using CHARINDEX.

    You can also split it as a delimited string:

    http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Tuesday, November 26, 2013 10:36 AM
    Moderator
  • The previous method will only work if you've upto maximum of three -s. Otherwise PARSENAME would break.

    This is a way which will work irrespective of number of - characetrs being present

    DECLARE @p_name VARCHAR(100)
    SET @p_name = '24-3463476-34765-457-ewrwet-123-234-67356-87H7-TEST24'
    
    SELECT CAST('<Root><Node>' +REPLACE(@P_Name,'-','</Node><Node>') + '</Node></Root>' AS xml).query('/Root/Node[position()=last()]').value('.','varchar(10)')


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, November 26, 2013 10:59 AM
  • try,

    DECLARE @p_name VARCHAR(100)
    SET @p_name = '356-877-TEST'
    
    select SUBSTRING(@p_name,LEN(@p_name)-CHARINDEX('-',reverse(@p_name))+2,LEN(@p_name))
    , STUFF(@p_name,1,LEN(@p_name)-CHARINDEX('-',reverse(@p_name))+1,'')
    SET @p_name = '67356-87H7-TEST24'
    
    select SUBSTRING(@p_name,LEN(@p_name)-CHARINDEX('-',reverse(@p_name))+2,LEN(@p_name))
    , STUFF(@p_name,1,LEN(@p_name)-CHARINDEX('-',reverse(@p_name))+1,'')


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 26, 2013 11:31 AM
  • DECLARE @p_name VARCHAR(100)
    SET @p_name = '356-877-TEST'
    SET @p_name = '67356-87H7-TEST24'
    select Substring(@p_name, charindex('-',@p_name,  charindex('-',@p_name)+1)+1 ,len(@p_name)),
    --Or
    Right(@p_name,  len(@p_name)-charindex('-',@p_name,  charindex('-',@p_name)+1)-1)

    Tuesday, November 26, 2013 5:24 PM
    Moderator
  • Since this functionality is very common for us, we use a recursive CTE to handle it.  We'll pass in the string (and optionally a delimiter character), and we'll get back a table of values.  The value that this option provides is not being restricted on the number of delimiters in the string.  Let me know if you'd like a code snip for it.


    Cheers! Brandon Tucker, Database Developer / DBA, OppenheimerFunds


    Tuesday, November 26, 2013 5:37 PM