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 (https://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.