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 a Dynamics GP Consultant (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[/postcode]

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.

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you’d like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) –
Your Email (required) –

Leave a Reply

Your email address will not be published. Required fields are marked *