Need help to split string, and access the array

• Adelia

  SSC-Addicted

  Points: 422

  More actions

  May 3, 2022 at 8:04 am

  Go to Answer

  #4025837

  Hi,

  I've have this kind of string,

  dr strange~('2', '8', '6', '1', '13', '18')~('dcc23eb8-ea79-46ea-81e8-022c0752a31e', 'cfe164e3-4e1d-4292-9016-1aa5aef5bf07', '77481c15-1eda-4c0b-8c5c-168479b3c7cf', 'f39f33c7-09de-4bb4-9f07-ea93d04fdb47', '719707f8-8931-4e25-ac15-28c55284af1a', '733fe23b-d053-4d54-bb96-fe03573cf79d', '4ffb80d8-3c1a-4dcc-a3d6-f44865105123', 'c0b35951-5ecf-4fe2-85d4-580051caf1f5', '1dc4a64a-6859-4f98-be87-8e6507b4dc42', '4150771f-8dee-45b2-8661-c9890e94d2f1', '1850dd6e-2e03-49da-b0b4-e16ed24f371d')~('6', '2', '4', '5', '3', '1')~('3', '4', '2', '1', '6', '5')~0~900000000~30~100~2021-01-01 00:00:00~2022-05-03 00:00:00

  The separator is ~ . It is tilde

  How to split and access the array[0] ?

  Please help

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  May 3, 2022 at 1:58 pm

  Answer

  #4025995

  Arrays do not exist in SQL Server, But strings can still be split and referenced by their ordinal ('ItemNumber' in the following).

  This code relies on the existence of the legendary DelimitedSplit8K function – code and thorough explanation can be found here.

  DECLARE @x VARCHAR(8000);
  
  SET @x
      = 'dr strange~(''2'', ''8'', ''6'', ''1'', ''13'', ''18'')~(''dcc23eb8-ea79-46ea-81e8-022c0752a31e'', ''cfe164e3-4e1d-4292-9016-1aa5aef5bf07'', ''77481c15-1eda-4c0b-8c5c-168479b3c7cf'', ''f39f33c7-09de-4bb4-9f07-ea93d04fdb47'', ''719707f8-8931-4e25-ac15-28c55284af1a'', ''733fe23b-d053-4d54-bb96-fe03573cf79d'', ''4ffb80d8-3c1a-4dcc-a3d6-f44865105123'', ''c0b35951-5ecf-4fe2-85d4-580051caf1f5'', ''1dc4a64a-6859-4f98-be87-8e6507b4dc42'', ''4150771f-8dee-45b2-8661-c9890e94d2f1'', ''1850dd6e-2e03-49da-b0b4-e16ed24f371d'')~(''6'', ''2'', ''4'', ''5'', ''3'', ''1'')~(''3'', ''4'', ''2'', ''1'', ''6'', ''5'')~0~900000000~30~100~2021-01-01 00:00:00~2022-05-03 00:00:00';
  
  SELECT split.ItemNumber
        ,split.Item
  FROM dbo.DelimitedSplit8K(@x, '~') split;

  Results are as follows:

  

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin