Split and Concat String on SQL and SSIS

Question

I am trying to split and concat a string.

Example: Data value1: "12abc,34efg,56hij"

Data value2: "12abc"

Data value

Expected result:

Numbers Column 1: "12,34,56" 
Numbers Column 2: "12"
Alphabets Column 1: "abc,efg,hij"
Alphabets Column 2 "abc"

Several attempts made:

1.

SELECT [String], value, CONCAT(SUBSTRING(value,1,2), ',') AS Numbers, CONCAT(SUBSTRING(value,3,3), ',') AS Alphabets, LEFT(String,LEN(String)-CHARINDEX(',',String))
  FROM [Test].[dbo].[TEST]
  CROSS APPLY string_split([String],',') value
  WHERE String = String

Result1

2.

SELECT [String], LEFT(String,LEN(String)-CHARINDEX(',',String)),  LEFT(String,2) AS Numbers, RIGHT(STRING,3) AS Alphabets
  FROM [Test].[dbo].[TEST]
  WHERE String = String

Result2

I have followed [How to split a string after specific character in SQL Server and update this value to specific column] because I thought it was pretty similar but I did not receive the results I want so I do not know how to proceed or what I went wrong.

I am unsure of how to concatenate different columns into 1 column.

Additional info: I am currently using SQL Server Management Studio v18.9.2.

*Apologies if my explanation is horrible.

I certainly have several ideas, however, I must admit I'm loath to provide them to this "question". You don't really ask a question, you just tell us what you want. You don't actually explain the logic, you just expect us to understand what you want. You also don't demonstrate any of the attempts you have made nor the research you have done to achieve this. If you take the time to improve your question I'd be happy to contribute. I suggest you take a moment to take the [tour] and read the [help] (I suggest the "How do I ask a good question?" article) and then [edit] your question accordingly. — Thom A, Jan 11 '22 at 17:38
Sorry for the bad post, I am new here & did not know that there was a tour. Will look at it and improve it in the future. Thank you for the suggestion and for editing the post for me. & I have attempted and googled for several methods to try and solve this issue. Such as, using "CROSS APPLY string_split(String,',') value" which split the value into new columns, but after that, I am unsure of how to concatenate the different columns. I have also tried "RIGHT(String,LEN(String)-CHARINDEX('^',String))" and "Substring(String,0,CharIndex('^',String))" but I do not know how to continue. — shir, Jan 11 '22 at 18:01
If you made attempts, include them in your question, @shir . Don't forget to address some of the other problems I mention as well when you perform the [edit]. — Thom A, Jan 11 '22 at 18:02
[Please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Jan 11 '22 at 18:40

Thom A · Accepted Answer · 2022-01-11T19:02:48.247

Firstly, let's get to the point; your design is flawed. Never store delimited data in your database, it breaks the fundamental rules of normalisation. I strongly suggest that what you actually do here is fix your design and normalise your data.

Next, the assumptions:

You are using SQL Server 2017+
The column string can only contain alphanumerical characters (A-z, 0-9)
You are using a case insensitive collation or all characters are lowercase

If this is the case, then you can just use TRANSLATE and REPLACE to remove the characters. You'll need to create some variables (or use the tally inline) to create the replacement strings first.

So, firstly, we get the 2 variables we need, which is one containing the letters a-z, and the other with the numbers 0-9. I use a tally to achieve this:

DECLARE @Alphas varchar(26),
        @Numerics varchar(10);

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (26)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3)
SELECT @Alphas = STRING_AGG(CHAR(96 + T.I),''),
       @Numerics = STRING_AGG(CASE WHEN T.I <= 10 THEN CHAR(47+T.I) END,'')
FROM Tally T;

Now we can use those values to TRANSLATE all those characters to a different character (I'm going to use a pipe (|)) and the REPLACE those pipe characters with nothing:

SELECT YT.String,
       REPLACE(TRANSLATE(YT.String, @Alphas,REPLICATE('|',LEN(@Alphas))),'|','') AS Numerics, 
       REPLACE(TRANSLATE(YT.String, @Numerics,REPLICATE('|',LEN(@Numerics))),'|','') AS Alphas
FROM dbo.YourTable YT;

Or, of course, you could just type it out. ;)

SELECT YT.String,
       REPLACE(TRANSLATE(YT.String, 'abcdefghijklmnopqrstuvwxyz',REPLICATE('|',LEN('abcdefghijklmnopqrstuvwxyz'))),'|','') AS Numerics, 
       REPLACE(TRANSLATE(YT.String, '0123456789',REPLICATE('|',LEN('0123456789'))),'|','') AS Numerics
FROM dbo.YourTable YT;

Hi, your solution worked! Thank you so much. I would like to know what if the source value was a complete string, e.g. "abcde"? How do I separate "ab" and "cde"? — shir, Jan 11 '22 at 19:27

score 0 · Answer 2 · answered Jan 11 '22 at 19:09

You can CROSS APPLY to a STRING_SPLIT that uses STRING_AGG (since Sql Server 2017) to stick the numbers and alphabets back together.

select Numbers, Alphabets
from TEST
cross apply (
  select
    string_agg(left(value, patindex('%[0-9][^0-9]%', value)), ',') as Numbers
  , string_agg(right(value, len(value)-patindex('%[0-9][^0-9]%', value)), ',') as Alphabets
  from string_split(String, ',') s
) ca;
GO

Numbers  | Alphabets  
:------- | :----------
12,34,56 | abc,efg,hij
12       | abc

db<>fiddle here

Split and Concat String on SQL and SSIS

2 Answers2