Splitting a string in an SQL query

Question

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

• Clockwork-Muse: What RDBMS are you on? Some 'better' solutions don't work on all products. Also, I recommend storing the code separated, if at all possible, so you don't have to do the split; if 90% of the time you are using the split code for joins, store it that way. Of course, if this is just for display, you should probably be using your application layer to perform the split.

Answer 1

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).

• danny117: Sweet I got this to work I added where length(section) > 0 to get rid of all the empty rows.
• danny117: I had to a a space because it couldn't parse when the last character was non blank. budgetcode || ' '
• Clockwork-Muse: @danny117 - not sure what you mean? The example is handling non-space-terminated data just fine?
• danny117: It failed on IBM Iseries DB2. Which doesn't have charindex substituted the locate function It failed on that implementation if a fixed length column didn't have a trailing blank so I just append a trailing blank. It probably works on other DB just fine.

Answer 2

First, if you have any influence at all in the database design, you may do better by storing the strings separately. It is a lot easier to glue strings together when needed than to split them apart when needed.

Second, if you are guaranteed to always have the same number of digits in each budget code, you could just use the absolute character positions, such as substring(pc.BudgetCode,6,5)

See this similar SO question and this more general SO question, which links to this authoritative page of many ways to split strings, many of which seem unnecessarily complex for your purpose.

You might also try writing really simple functions. One advantage is that MSSQL seems to cache the results of functions, so a query with functions can run a lot faster the second time:

create function getslidcode (@budgetcode nvarchar(100))
 returns @slidcode nvarchar(100) as
begin
 declare @pos int
 select @pos = charindex('-', @budgetcode)
 select @pos = charindex('-', @budgetcode, @pos + 1)
 select @slidcode = substring(@budgetcode, @pos + 1, 100)
end
select budgetcode, getslidcode(budgetcode) as slidcode from pc

• Chad: This is for a report that will get run 10 times (once a week for the next 10 weeks then go away) I Have no influence on the design of the db and if it was this isnt even close the the first thing i would tackle. I really am trying to avoid adding more function to this already programatically bloated db (With unused and unmaintained sps and functions not tomention tempo tables that have become part of the standard process this whole db belongs on thedailywtf.com) for what is essentially a 1 off report that will probably never be read.
• krubo: Splitting strings at db level is relatively hard, as you are seeing. If it's for a one-off (or 10-off), you should just do the splitting in another language after extracting the result from the db.
• Chad: that would be far to easy... They want a query they can run themselves... of course i will be assinged the task to run it everytime.
• krubo: In this situation, I normally provide a single Excel file containing the instructions 1. copy and run the SQL at right, 2. paste the SQL results here, 3. the desired report is below.