question

RoyChen-2557 avatar image
0 Votes"
RoyChen-2557 asked ·

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
· 4
10 |1000 characters needed characters left characters exceeded

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

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

0 Votes 0 · ·

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 Votes 0 · ·

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 Votes 0 · ·

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 Votes 0 · ·

1 Answer

KalyanChanumolu-MSFT avatar image
1 Vote"
KalyanChanumolu-MSFT answered ·

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

84741217-586dc700-afcc-11ea-8111-d9ffb96be3ab.png



· Share
10 |1000 characters needed characters left characters exceeded

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