3
\$\begingroup\$

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
\$\endgroup\$
1
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ Sep 1, 2011 at 17:19

2 Answers 2

4
\$\begingroup\$

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

\$\endgroup\$
4
  • \$\begingroup\$ Sweet I got this to work I added where length(section) > 0 to get rid of all the empty rows. \$\endgroup\$
    – danny117
    Sep 23, 2015 at 22:55
  • \$\begingroup\$ I had to a a space because it couldn't parse when the last character was non blank. budgetcode || ' ' \$\endgroup\$
    – danny117
    Sep 24, 2015 at 17:00
  • \$\begingroup\$ @danny117 - not sure what you mean? The example is handling non-space-terminated data just fine? \$\endgroup\$ Sep 24, 2015 at 23:18
  • \$\begingroup\$ 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. \$\endgroup\$
    – danny117
    Sep 25, 2015 at 14:12
3
\$\begingroup\$

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
\$\endgroup\$
4
  • \$\begingroup\$ 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. \$\endgroup\$
    – Chad
    Sep 6, 2011 at 12:51
  • \$\begingroup\$ 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. \$\endgroup\$
    – krubo
    Sep 6, 2011 at 14:34
  • \$\begingroup\$ 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. \$\endgroup\$
    – Chad
    Sep 6, 2011 at 15:18
  • \$\begingroup\$ 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. \$\endgroup\$
    – krubo
    Sep 6, 2011 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.