-1

I want to split string similarly to answer given here: Split String into given length but do not split word

But instead of that approach I need to also apart of space I need to consider "-" as a symbol which splits in defined range eg. 25 characters, grouped by id. Columns should contain text strings divided by successive splits.

My code, based on above link:

CREATE TABLE test (
id int,
  base_string_column   VARCHAR(MAX)
);

INSERT INTO
  test
VALUES
(4, 'The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies'),
(15,  'Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown'),
(16, 'The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger');


DECLARE @chars BIGINT = 25;

WITH
  parts AS
(
  SELECT
    id,
    base_string_column,
    LEN(base_string_column)        AS length,
    CAST(0 AS BIGINT)   AS last_space,
    CAST(1 AS BIGINT)   AS next,
    base_string_column             AS fragment
    
  FROM
    test

  UNION ALL

  SELECT
    id,
    parts.base_string_column,
    parts.length,
    last_space.pos,
    parts.next + COALESCE(last_space.pos, @chars),
    SUBSTRING(parts.base_string_column, parts.next, COALESCE(last_space.pos - 1, @chars))
    
  FROM
    parts
  CROSS APPLY
  (
    SELECT
      @chars + 2
      -
      NULLIF(
        CHARINDEX(
          ' ',
          REVERSE(
            SUBSTRING(
              parts.base_string_column + ' ',
              parts.next,
              @chars + 1
            )
          )
        )
        , 0
      )
  )
    last_space(pos)
  WHERE
    parts.next <= parts.length
)

SELECT
  *, len(fragment) AS chars
  into #outcome_part_1
FROM
  parts
WHERE
  next > 1
ORDER BY
  base_string_column,
  next

The following part of code, where I tried to go further, the following picture of my result shows only the use of lag function for this batch, but with the wrong "chars" column as an example.

select * ,
  lag(next, 1) over (Partition By id order by  "some unknown column that perhaps would help" desc) as Next_place_after_split
  into #outcome_part_2
  from #outcome_part_1

 select *,
  case when Next_place_after_split is null then fragment
  else ''
  end as Column1,
  case when Next_place_after_split < next then fragment
  else ''
  end as Column2,
  case when Next_place_after_split < next then fragment
  else ''
  end as Column3,
  case when Next_place_after_split < next then fragment
  else ''
  end as Column4
  from #outcome_part_2
My outcome :
| id| base_string_column                                                                             |length|last_space|next|fragment                 |chars|Next_place_after_split | Column1                   | Column2                   | Column3                   | Column5                  | 
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |25        |26  |The Incredibly Creatures |24   |NULL                   | Cafeteria or How Are You  |                           |                           |                          |
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |23        |46  |Who Stopped Living And   |22   |26                     |                           | Who Stopped Living And    | Who Stopped Living And    | Who Stopped Living And   |
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |20        |69  |Accidentally Became      |19   |49                     |                           | Accidentally Became       | Accidentally Became       | Accidentally Became      |
| 4 |The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies            |84    |26        |95  |Mixed-Up Zombies         |16   |69                     |                           | Mixed-Up Zombies          | Mixed-Up Zombies          | Mixed-Up Zombies         |
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |26        |52  |Going to Keep Her Down on|25   |NULL                   | Going to Keep Her Down on |                           |                           |                          |
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |26        |78  |the Farm after She’s Seen|25   |52                     |                           | the Farm after She’s Seen | the Farm after She’s Seen | the Farm after She’s Seen|
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |25        |26  |Cafeteria or How Are You |24   |78                     |                           |                           |                           |                          |
| 4 |Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown|96    |26        |104 |Paris Twice Unknown      |19   |26                     |                           | Paris Twice Unknown       | Paris Twice Unknown       | Paris Twice Unknown      |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |26        |50  |Women and their Voyage to|25   |NULL                   | Women and their Voyage to |                           |                           |                          |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |24        |74  |the Waters of the Great  |23   |50                     |                           | the Waters of the Great   | the Waters of the Great   | the Waters of the Great  |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |23        |24  |The Saga of the Viking   |22   |74                     |                           |                           |                           |                          |
| 4 |The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger      |90    |26        |100 |Sea Serpent Roger        |17   |24                     |                           | Sea Serpent Roger         | Sea Serpent Roger         | Sea Serpent Roger        |
Desired outcome:
| id | base_string_column                                                                               | fragment 1               | fragment 2                | fragment 3                | fragment 4            | 
| 15 | Cafeteria or How Are You Going to Keep Her Down on the Farm after She’s Seen Paris Twice Unknown | Cafeteria or How Are You | Going to Keep Her Down on | the Farm after She’s Seen | Paris Twice Unknown   |
| 4  | The Incredibly Creatures Who Stopped Living And Accidentally Became Mixed-Up Zombies             | The Incredibly Creatures | Who Stopped Living And    | Accidentally Became Mixed | Up Zombies            |
| 16 | The Saga of the Viking Women and their Voyage to the Waters of the Great Sea Serpent Roger       | The Saga of the Viking   | Women and their Voyage to | the Waters of the Great   | Sea Serpent Roger     |

I've tried to retransform code from linked code and find out a way to split columns and I've split them but with wrong input to lag function and also I didn't have idea how could I use 'em dash' "-" to also consider as a splitting character at the given range(in the example the range is 25)

So in summary, the goal is:

  • Finding the right way to separate the columns.
  • Finding a way to include a dash"-" as simultaneously working a column separator character with space in a range of 30 characters, as in the example the space separates the columns.

I hope that someone would know how to handle this problem.

Thank you in advance

Nkifor
  • 38
  • 1
  • 4
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Feb 22 '23 at 19:10
  • I think you're on the wrong track. What you need is three things: 1. Adapt the code from the other place so it checks both charindex(' ') and charindex('-') and then takes the least (or the most depending on what you want) of those two. It should be pretty easy. 2. you need to create an extra field in the CTE that is just a simple counter that goes 1,2,3,4... N. Then you have you pivot-ready data that looks like: id, fragment, counter. 3. Finally, you just pivot this table by id, MAX(fragment) where counter IN ([1],[2],[3],[4], ... [N]) – siggemannen Feb 22 '23 at 19:49
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. – Yitzhak Khabinsky Feb 22 '23 at 20:17
  • I would suggest doing this outside of sql server. It just isn't very good at string manipulation like this. It almost certainly is going to take some recursion which is going to cause performance challenges. – Sean Lange Feb 22 '23 at 20:31

1 Answers1

0

Instead of using lag function i've used lead() one and also i've ranked results.

  select * ,
  lead(next, 1) over (Partition By id order by  next) as Next_place_after_split,
  rank() over (Partition By id order by next) as Rank_fragment 
  into #outcome_part_2
  from #outcome_part_1

After that i used simply Case statement with rank column and ascending column "next_place_after_split"

  select *,
  case when Rank_fragment = 1 then fragment
  else ''
  end as Column1,
  case when Rank_fragment = 2 then fragment
  else ''
  end as Column2,
  case when Rank_fragment = 3 then fragment
  else '' 
  end as Column3,
  case when Rank_fragment = 4 then fragment
  else ''
  end as Column4
  from #outcome_part_2

This result satysfing me, but if someone would like to show part with additional dash separating column as second option. Your reply will always be welcome.

Nkifor
  • 38
  • 1
  • 4