How to extract nth occurrence substring from a string column in SQL Table

Question

Hi All

I have a nvarchar(MAX) column (as below) in SQL table, I need to extract nth occurrences substring from this column; I manage to extract the 1st occurrence with CHARINDEX statement 10137-microsoft-technet-qa-roychen.pdfbut unable to extract 2nd, 3rd and 4th.....

Sample nvarchar (MAX) Column refer to attachment please.

Very Appreciate your kindness advice
Roy

Answer 1

Any reason why you want to use the string functions when you can use JSON functions instead?

DECLARE @json NVARCHAR(MAX);

SET @json = N'[{"Guid":"c1a2a60a-5d04-44d6-91ab-06c4872c14ea","LineNumber":1,"PositionSum":13.75,"Text":"FLAG NATIONAL 4''X 6'' BUNTING, UNITED ARAB EMIRATES","BudgetGroupNumber":"1902","BudgetGroupName":"","CostType":null},{"Guid":"06d1f2c2-7031-4eb0-8506-9161ecaffb6e","LineNumber":3,"PositionSum":11.25,"Text":"FLAG NATIONAL 4''X 6'' BUNTING, SINGAPORE","BudgetGroupNumber":"1902","BudgetGroupName":"","CostType":null},{"Guid":"526ddd86-f64a-49f1-95c3-9c92fd8bc0ed","LineNumber":2,"PositionSum":17.5,"Text":"FLAG NATIONAL 4''X 6'' BUNTING, QATAR","BudgetGroupNumber":"1902","BudgetGroupName":"","CostType":null},{"Guid":"8ce29032-a0c0-4984-a7d1-c825de7c40d9","LineNumber":4,"PositionSum":21.25,"Text":"FLAG NATIONAL 4''X 6'' BUNTING, PORTUGAL","BudgetGroupNumber":"1902","BudgetGroupName":"","CostType":null},{"Guid":"73259914-fbb2-4207-889bcae3280c7846","LineNumber":10001,"PositionSum":-3.1875,"Text":"Header Discount","BudgetGroupNumber":"1902","BudgetGroupName":"","CostType":"Discount"}]';

SELECT *
FROM OPENJSON(@json)
WITH (
Guid varchar(50) 'strict $.Guid',
Text NVARCHAR(50) '$.Text'
);

You should get this result