0

Using SQL Server 2022, attempt to create a computed column for indexing/queries on the 3rd part of a string separated by slashes (it's a URI).

ALTER TABLE MyTable 
    ADD SpotName AS (CAST((SELECT Value FROM STRING_SPLIT(FullUri, '/', 7)) AS VarChar(128))) PERSISTED

And the error I get is:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

I want to do this for 5 different parts of URIs, so what is a smart way to index/optimize queries for different substring parts?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Snowy
  • 5,942
  • 19
  • 65
  • 119
  • 1
    You can't do that because `String_Split()` returns multiple row. What are you trying to achieve here ? Please do include some sample data and expected result – Squirrel Aug 03 '23 at 02:27
  • 2
    You can put your sub-query into a function... can't speak for its performance... well I can, it will be rubbish... but if thats what you need. – Dale K Aug 03 '23 at 02:28
  • 1
    `String_Split(FullUri,'/',7)` You should be getting error on the `enable_ordinal` value, it does allow value other than null, 0 or 1 – Squirrel Aug 03 '23 at 02:35

1 Answers1

2

You can do

Alter Table MyTable 
Add SpotName As (  Cast(JSON_VALUE('["' + REPLACE(FullUri, '/', '","') + '"]', '$[6]')as VarChar(128))) PERSISTED

To split the string in a manner that is regarded as deterministic so can be persisted and/or indexed and without using Scalar UDFs (that block parallelism on the table by default).

Note the position is 0 based - not 1 based. Fiddle

Martin Smith
  • 438,706
  • 87
  • 741
  • 845