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 this similar SO question and this more general SO question 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
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
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
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