Post reply

Need help to substring, remove string and get string

  • July 6, 2015 at 12:09 am

    #309666

    Hi,

    My table and data shown as follow,

    idx | acct_code

    ------------------------------

    104-82205-20301

    204-82205-20302

    304-82205-20303

    404-82205-20304

    504-82205-20305

    /*

    Please get it from attachment - 06072015_1.zip

    */

    I want to extract the acct_code from the substring start = 4. Then, when program find string ' - ', it will stop

    The expected result will be

    acct_code

    ----------------------------

    82205

    82205

    82205

    82205

    82205

    25104

    28601

    27104

    Please help

  • July 6, 2015 at 2:21 am

    #1810366

    You data appears to have a fixed structure and the code you're looking for seems to be always between chars 4 and 9. If it's guaranteed to be like that, you can use a simple SUBSTRING. Otherwise, if the dashes delimit tokens of variable length, you can use CHARINDEX to find the first and the second dash.

    Example:

    SELECT

    acct_code_fixed = SUBSTRING(acct_code, 4, 5),

    acct_code_variable = SUBSTRING(acct_code, CHARINDEX('-',acct_code,1) + 1, CHARINDEX('-',acct_code,CHARINDEX('-',acct_code,1) - CHARINDEX('-',acct_code,1)) + 2)

    FROM table_1

    -- Gianluca Sartori

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

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