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
Hi It is unclear what is desired result? Did you manage getting FLAG NATIONAL 4'X 6' BUNTING?
Hi
Yes, I manage to get the "FLAG NATIONAL 4'X 6' BUNTING, UNITED ARAB EMIRATES"; but cannot get 2nd, 3rd and 4th sub-string which are: - 2nd - "FLAG NATIONAL 4'X 6' BUNTING, SINGAPORE" 3rd - "FLAG NATIONAL 4'X 6' BUNTING, QATAR" 4th - "FLAG NATIONAL 4'X 6' BUNTING, PORTUGAL"
Thank You
Perhaps this declare @strvarchar(max)='LineNumber":1,"PositionSum":13.75, "Text":"FLAG NATIONAL 4X 6 BUNTING, UNITED ARAB EMIRATES","BudgetGroupNumber" :"1902","BudgetGroupName":"","CostType":null},{"Guid":"06d1f2c2-70314eb0-8506-9161ecaffb6e'
select from( select ,row_number() over (order by (select 1)) rn from string_split(@str,':') ) as dere where rn=5 --4--6
Sorry, this nvarchar(MAX) column is variable in length and contents, however the desired sub-string is fixed between "Text": and "BudgetGroupNumber"
Thank You
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
8 people are following this question.
