azurecurve

As 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