Splitting Strings then Creating Segments out of those. T-SQL

Question

How do I split a csv string into this format in SQL Server?

Initial String value (A, B, C, D) into :

A-B
B-C
C-D

While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: https://stackoverflow.com/help/minimal-reproducible-example Please provide the following: (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 #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky, Mar 12 '21 at 15:51
Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Charlieface, Mar 12 '21 at 15:54
Is the pairing within the results based on the individual values (`'A'` < `'B'`), the order within the initial string or some other desire? Are the parentheses part of the input? Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Mar 12 '21 at 16:28

score 1 · Answer 1 · answered Mar 12 '21 at 16:00

1

You can try using string_split in conjunction with lead()

select value + '-' + lead(value) over (order by value) new_value 
from string_split('A,B,C,D',',')

SQL FIDDLE:

http://sqlfiddle.com/#!18/0a28f/2607

answered Mar 12 '21 at 16:00

Pradeep Kumar

6,836
4
21
47

1

From [`String_Split`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15): "The output rows might be in any order. The order is _not_ guaranteed to match the order of the substrings in the input string." Ordering by `value` may or may not be what the OP has in mind. – HABO Mar 12 '21 at 16:27
@HABO, While the documentation does say that order is not guaranteed, I'm yet to see a situation where this happens (without a reason). Can you please show an example where the order of values doesn't come up as expected? – Pradeep Kumar Mar 14 '21 at 17:10
I used to do industrial automation. Imagine the fun of explaining to a client that they spilled a few tons of molten steel because I used a function that was _documented_ as not providing a feature I needed. It saved me a couple of minutes over grabbing another copy of [Jeff Moden's string splitter](https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function). Well, only killed two and maimed a few more. Perhaps it breaks on a multiprocessor, or the next update to SQL Server. It's _documented_, so don't depend on it. – HABO Mar 14 '21 at 17:28

score 0 · Answer 2 · answered Mar 12 '21 at 19:35

Grab a copy of NGrams8K then you could simply do this.

DECLARE @string VARCHAR(100) = 'A, B, C, D';

SELECT TheString = CONCAT(ng.Token,'-',ng.Nxt)
FROM
(
  SELECT ng.Token, Nxt = LEAD(ng.Token,1) OVER (ORDER BY ng.Position)
  FROM   dbo.ngrams8k(@string,1) AS ng
  WHERE   ng.Token LIKE '%[a-z]%'
) AS ng
WHERE ng.Nxt IS NOT NULL;

Returns:

TheString
---------------------
A-B
B-C
C-D

Order is guaranteed without a sort in the execution plan.

Splitting Strings then Creating Segments out of those. T-SQL

2 Answers2