SQL View to Return Microsoft Dynamics GP Item Number Split by Hyphens

Microsoft Dynamics GPAs with most of the other views I have posted, this is one I have written a few times over the years and am now posting it to make it easy to find next time I need it.

This view returns the Item Number from the Item Master (IV00101) table split into sections by hyphens. It assumes the item number has up to three sections, but can easily be extended.

With the view deployed, it can easily be included in any reporting tool, such as SmartList Designer/Builder or a refreshable Excel Report.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
-- drop view if it exists IF OBJECT_ID(N'uv_AZRCRV_ItemNumberSplitByHyphens', N'V') IS NOT NULL DROP VIEW uv_AZRCRV_ItemNumberSplitByHyphens GO -- create view CREATE VIEW uv_AZRCRV_ItemNumberSplitByHyphens AS SELECT DISTINCT ITEMNMBR ,RTRIM(SUBSTRING(REPLACE(ITEMNMBR, '-', REPLICATE(' ', LEN(ITEMNMBR))), (0) * LEN(ITEMNMBR)+1, LEN(ITEMNMBR))) AS SECTION_1 ,RTRIM(SUBSTRING(REPLACE(ITEMNMBR, '-', REPLICATE(' ', LEN(ITEMNMBR))), (1) * LEN(ITEMNMBR)+1, LEN(ITEMNMBR))) AS SECTION_2 ,RTRIM(SUBSTRING(REPLACE(ITEMNMBR, '-', REPLICATE(' ', LEN(ITEMNMBR))), (2) * LEN(ITEMNMBR)+1, LEN(ITEMNMBR))) AS SECTION_3 FROM IV00101 AS ['Item Master'] WITH (NOLOCK) --Item Master (IV00101) GO GRANT SELECT ON uv_AZRCRV_ItemNumberSplitByHyphens TO DYNGRP GO

It would also be quite easy to amend the view to split out other strings based on a character using a variation of the above SQL script.