CODEWITHSUNDEEP
sql-servert-sql
Koo SengSeng
Koo SengSeng

Reputation: 953

TSQL: How insert separator between each character in a string

I have a string like this:

Apple

I want to include a separator after each character so the end result will turn out like this:

A,p,p,l,e

In C#, we have one liner method to achieve the above with Regex.Replace('Apple', ".{1}", "$0,");

I can only think of looping each character with charindex to append the separator but seems a little complicated. Is there any elegant way and simpler way to achieve this?

Upvotes: 1

Views: 3448

Answers (5)

Alan Burstein
Alan Burstein

Reputation: 7918

If you have SQL Server 2017 and a copy of ngrams8k it's ultra simple:

declare @word varchar(100) = 'apple';

select newString = string_agg(token, ',') within group (order by position)
from dbo.ngrams8k(@word,1);

For pre-2017 systems it's almost as simple:

declare @word varchar(100) = 'apple';

select newstring = 
( select token + case len(@word)+1-position when 1 then '' else ',' end
  from dbo.ngrams8k(@word,1)
  order by position
  for xml path(''))

Upvotes: 1

Eralper
Eralper

Reputation: 6612

In following SQL scripts, I get each character using SUBSTRING() function using with a number table (basically I used spt_values view here for simplicity) and then I concatenate them via two different methods, you can choose one

If you are using SQL Server 2017, we have a new SQL string aggregation function First script uses string_agg function

declare @str nvarchar(max) = 'Apple'

SELECT
string_agg( substring(@str,number,1) , ',') Within Group (Order By number)
FROM master..spt_values n
WHERE 
Type = 'P' and 
Number between 1 and len(@str)

If you are working with a previous version, you can use string concatenation using FOR XML Path and SQL Stuff function as follows

declare @str nvarchar(max) = 'Apple'

; with cte as (
    SELECT
    number,
    substring(@str,number,1) as L
    FROM master..spt_values n
    WHERE 
    Type = 'P' and 
    Number between 1 and len(@str)
)
SELECT
  STUFF(
    (
    SELECT
      ',' + L
    FROM cte
    order by number
    FOR XML PATH('')
    ), 1, 1, ''
  )

Both solution yields the same result, I hope it helps

enter image description here

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try this

declare @var varchar(50) ='Apple'

;WITH CTE
AS
(
    SELECT
        SeqNo = 1,
        MyStr = @var,
        OpStr = CAST('' AS VARCHAR(50))

    UNION ALL

    SELECT
        SeqNo = SeqNo+1,
        MyStr = MyStR,
        OpStr = CAST(ISNULL(OpStr,'')+SUBSTRING(MyStR,SeqNo,1)+',' AS VARCHAR(50))
        FROM CTE
        WHERE SeqNo <= LEN(@var)
)
SELECT
    OpStr = LEFT(OpStr,LEN(OpStr)-1)
    FROM CTE
        WHERE SeqNo = LEN(@Var)+1

Upvotes: 0

Koo SengSeng
Koo SengSeng

Reputation: 953

Thanks HABO for the suggestions. I'm able to generate the result that I want using the code but takes a little bit of time to really understand how the code work.

After some searching, I manage to found one useful article to insert empty spaces between each character and it's easier for me to understand.

I modify the code a little to define and include desire separator instead of fixing it to space as the separator:

 DECLARE @pos INT = 2 -- location where we want first space 
 DECLARE @result VARCHAR(100) = 'Apple'
 DECLARE @separator nvarchar(5) = ','
    WHILE @pos < LEN(@result)+1 
    BEGIN 
        SET @result = STUFF(@result, @pos, 0, @separator); 
        SET @pos = @pos+2; 
    END 
    select @result; -- Output: A,p,p,l,e

Reference

Upvotes: 2

HABO
HABO

Reputation: 15816

One ugly way to do it is to split the string into characters, ideally using a numbers table, and reassemble it with the desired separator.

A less efficient implementation uses recursion in a CTE to split the characters and insert the separator between pairs of characters as it goes:

declare @Sample as VarChar(20) = 'Apple';
declare @Separator as Char = ',';

with Characters as (
  select 1 as Position, Substring( @Sample, 1, 1 ) as Character
  union all
  select Position + 1,
    case when Position & 1 = 1 then @Separator else Substring( @Sample, Position / 2 + 1, 1 ) end
    from Characters
    where Position < 2 * Len( @Sample ) - 1 )
  select Stuff( ( select Character + '' from Characters order by Position for XML Path( '' ) ), 1, 0, '' ) as Result;

You can replace the select Stuff... line with select * from Characters; to see what's going on.

Upvotes: 0

Related Questions