Need help to split string, and access the array

• Adelia

  SSC-Addicted

  Points: 407

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

  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:

  

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.