June 18, 2015 at 1:10 pm
Hi all,
Need some help here,. See belwo :
Row Value :
Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun
and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.
Should I just go for charindex on break(
)
or is there a another way ?
Thanks,
V
June 18, 2015 at 2:13 pm
venkyzrocks (6/18/2015)
Hi all,Need some help here,. See belwo :
Row Value :
Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun
and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.
Should I just go for charindex on break(
)
or is there a another way ?
Thanks,
V
Quick thought, will the value always be prefixed with ")Department: " ?
😎
June 18, 2015 at 2:23 pm
venkyzrocks (6/18/2015)
Hi all,Need some help here,. See belwo :
Row Value :
Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun
and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.
Should I just go for charindex on break(
)
or is there a another way ?
Thanks,
V
Pretty sparse on details but assuming the value "Department:" will always be there something like this should do it.
declare @RowValue varchar(200) =
'Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun'
select SUBSTRING(@RowValue, CHARINDEX('Department:', @RowValue, 0) + 12, CHARINDEX('-', @RowValue, CHARINDEX('Department:', @RowValue, 0)) - CHARINDEX('Department:', @RowValue, 0) - 12)
If at all possible it would be hugely easier on you if you could parse all the gibberish into columns so you don't have to wrestle with this all the time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2015 at 2:34 pm
Department would be prefixed in this case.
Thanks All for taking time. Was using substring on charindex, let me know if there are better solutions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply