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

Question

question

RoyChen-2557 asked Jun 16, '20 UriDimant commented Jun 16, '20

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

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

azure-sql-database

microsoft-technet-qa-roychen.pdf (103.7 KiB)

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

UriDimant · Jun 16, 2020 at 06:26 AM

Hi It is unclear what is desired result? Did you manage getting FLAG NATIONAL 4'X 6' BUNTING?

0 ·

RoyChen-2557 UriDimant · Jun 16, 2020 at 06:41 AM

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

0 ·

UriDimant · Jun 16, 2020 at 06:31 AM

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

0 ·

RoyChen-2557 UriDimant · Jun 16, 2020 at 06:53 AM

Sorry, this nvarchar(MAX) column is variable in length and contents, however the desired sub-string is fixed between "Text": and "BudgetGroupNumber"

Thank You

0 ·

Answer 1 · 2020-06-16T06:55:53.043Z

KalyanChanumolu-MSFT answered Jun 16, '20

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question