Post reply

Query to Pull character to the right of a hyphen

  • March 29, 2011 at 5:29 pm

    #235964

    I am having trouble writing a query to pull only characters to the right of a - symbol. Column sample below.

    10001-10001

    10002-1002

    10003-103

    1004-100000004

    Basically I need to only pull the following characters.

    10001

    1002

    103

    100000004

    I know how to pull them if the second string of characters were all the same length but not if they are of varying lengths. Thank you for your help.

  • March 29, 2011 at 5:45 pm

    #1305210

    A quick and dirty solution is to do this:

    RIGHT( REVERSE( cola), CHARINDEX( '-', REVERSE( cola) ) - 1 )

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 29, 2011 at 5:53 pm

    #1305212

    Maybe I should have been more specific or more detailed. Thanks for the reply though.

    This is what worked and returned me what I needed.

    select code,

    (substring(code,

    charindex('-',Code)+1 ,

    len(Code)))

    from accountmain

    Data returned is:

    code (No column name)

    10001-10010 10010

    10001-20001 20001

    10001-20007 20007

    10001-20104 20104

  • March 29, 2011 at 6:10 pm

    #1305213

    tschuler-738392 (3/29/2011)

    Maybe I should have been more specific or more detailed. Thanks for the reply though.

    This is what worked and returned me what I needed.

    Nah, no test data so I offered an idea off the cuff. This is what I meant (I had an extra reverse):

    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

    DROP TABLE #tmp

    CREATE TABLE #tmp (sometext VARCHAR(30))

    INSERT INTO #tmp

    SELECT '10001asd-10010' UNION ALL

    SELECT '10001-20001dfs' UNION ALL

    SELECT '10001-adsfa20007' UNION ALL

    SELECT '10001-104'

    SELECT

    RIGHT( sometext, CHARINDEX( '-', REVERSE( sometext))-1)

    FROM

    #tmp

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 29, 2011 at 6:58 pm

    #1305219

    Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

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