Splitting a string in an SQL query

BudgetCode is in the format 'xxxx-yyyyy-zzzzz'. This splits it correctly but I think that there has to be a more efficient way.

Select 
       substring(pc.BudgetCode,1, CHARINDEX('-',pc.BudgetCode)-1) as Cost_Center,
       substring(Substring(pc.BudgetCode,Charindex('-',pc.BudgetCode)+1,len(pc.BudgetCode)),1, CHARINDEX('-',Substring(pc.BudgetCode,Charindex('-',pc.BudgetCode)+1,len(pc.BudgetCode)))-1) as Account_Code,
       Substring(Substring(pc.BudgetCode,Charindex('-',pc.BudgetCode)+1,len(pc.BudgetCode)),Charindex('-',Substring(pc.BudgetCode,Charindex('-',pc.BudgetCode)+1,len(pc.BudgetCode)))+1,len(Substring(pc.BudgetCode,Charindex('-',pc.BudgetCode)+1,len(pc.BudgetCode)))) as Slid_Code
from pc

Hmm... Not sure how much faster this will be, but it may be easier to wrap your head around.

You can use a recursive CTE:

WITH Splitter (id, start, e, section, original, num) as (
               SELECT id, 1, CHARINDEX('-', budgetCode), CAST('' AS VARCHAR(20)), budgetCode, 0
               FROM PC
               UNION ALL
               SELECT id, e + 1, 
                      CASE WHEN CHARINDEX('-', original, e + 1) > 0
                           THEN CHARINDEX('-', original, e + 1)
                           ELSE LEN(original) + 1 END,
                      SUBSTRING(original, start, e - start), 
                      original, num + 1
               FROM Splitter
               WHERE e > start)

Results:

SELECT * 
FROM splitter

Makes a table that looks like this:

Id  BudgetCode
=====================
1   xxxx-yyyyy-zzzzz

Into this:

Id  Start  End   Section   Original           Num
1   1      5               xxxx-yyyyy-zzzzz   0
1   6      11     xxxx      xxxx-yyyyy-zzzzz   1
1   12     17    yyyyy     xxxx-yyyyy-zzzzz   2
1   18     17    zzzzz     xxxx-yyyyy-zzzzz   3

SQL Fiddle Example

You can then join to the result set multiple times based on Num or something to get the particular index you need. It'll automatically handle any additional 'subfields' (to the limit of the recursion, of course).

StackExchange Code Review Q#4533, answer score: 4