Need help to substring, remove string and get string

• Qira

  SSCommitted

  Points: 1930

  More actions

  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

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  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