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: 246985

  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

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel