If you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.
This third example, shows how to use the new in SQL Server 2016 string_split command:
/* 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK). */ SELECT value ,ITEMNMBR ,ITEMDESC ,ITMCLSCD FROM IV00101 CROSS APPLY string_split(RTRIM(ITEMNMBR), '-') WHERE value = 'SHP'
The example is part of the code I used when working on a client project a while ago; the client had a large number of Inventory Items and I needed to select a subset of the Items from the Inventory Master (IV00101).
When the clioent created their items they did so using a hyphen delimiter. Using the string_split command, I was able to separate out the segments of the Item Number and select only one of them in the WHERE clause.