Split string into letters separated by a full stop (SQL)

Question

I am trying to write a stored procedure which accepts a string parameter and returns it with each character separated by a full stop.

So for example I want the SP to accept parameter DOG and return D.O.G.

I have tried to use the STRING_SPLIT function as follows:

select STRING_SPLIT(@myString, '')

but it doesn't seem to be compatible with the version of SQL I'm using (2014) (the error message says it is not a recognised function). Even if it did work I'm not sure how to then insert full stops.

It seems like there should be an easy way to do this but I just can't find it!

Please let me know if you need any further information.

Many thanks.

`STRING_SPLIT` was introduced in SQL Server 2016, however, you are calling it incorrectly anyway. It is a Table-value function, so is called in the `FROM`: `SELECT * FROM String_Split(@MyString,'.');` — Thom A, Oct 04 '18 at 16:05

Valerica · Answer 1 · 2018-10-04T16:16:47.403

5

If I understood well this is the best approach I can think of right now, using the answer from this question T-SQL Split Word into characters

with cte as (
    select
        substring(a.b, v.number+1, 1) as col
        ,rn = ROW_NUMBER() over (order by (select 0))
    from (select 'DOG' b) a
    join master..spt_values v
        on v.number < len(a.b)
    where v.type = 'P'
)
select distinct
    STUFF((SELECT '.' + col FROM cte order by rn FOR XML PATH('')),1,1,'') as col
from cte

edited Oct 04 '18 at 16:16

answered Oct 04 '18 at 16:10

Valerica

1,618
1
13
20

Doing some performance tests the answer from Larnu is way faster. I even switched out to use the same no read tally table instead of spt_values. For small batches it is likely irrelevant but for large datasets it could be a huge difference. – Sean Lange Oct 04 '18 at 16:58
Appreciate the feedback @SeanLange but I certainly can't claim responsibility for the speed. That was all Alan Burstein and those that contributed to the articles he wrote on SQL Server Central. – Thom A Oct 04 '18 at 17:21

score 5 · Answer 2 · answered Oct 04 '18 at 16:11

Misread the question at first. This answer uses NGRams8K to split the parameter into characters, and then FOR XML PATH to join it back up:

SELECT (SELECT token +'.'
        FROM dbo.NGrams8k('DOG',1)
        ORDER BY position
        FOR XML PATH(''))

score 2 · Answer 3 · answered Oct 04 '18 at 18:44

2

There is no built-in function to accomplish what you are after, but you can easily do it using a simple while loop.

Iterate through each character in the string and use CONCAT:

    DECLARE @InputString NVARCHAR(200);
    DECLARE @Seperator CHAR(1);
    DECLARE @OutputString NVARCHAR(MAX);
    DECLARE @Counter INT;

    SET @InputString = N'TestString';
    SET @Seperator = '.';

    SET @Counter = 1;
    WHILE @Counter <= LEN(@InputString)
        BEGIN
            SET @OutputString = CONCAT(
                                         @OutputString
                                       , SUBSTRING(@InputString, @Counter, 1)
                                       , @Seperator
                                     );
            SET @Counter = @Counter + 1;
        END;

    SELECT @OutputString;

answered Oct 04 '18 at 18:44

Tim Mylott

2,553
1
5
11

I'd recommend against this one. A `WHILE` loop would likely be the slowest option for achieving the end goal here. It will achieve the answer, but a dataset approach will likely yield far better performance, especially with a far longer string. – Thom A Oct 04 '18 at 19:19
Thank you this has worked a treat. Larnu - I totally take your point about performance but in this case the input string will only ever be very small (some initials) and performance isn't a consideration. – HelenS Oct 05 '18 at 07:59
@HelenS but yet you have 2 dataset based answers. What was wrong with those? – Thom A Oct 07 '18 at 09:22
@HelenS fair enough, I though you meant that you had used this one an the accepted answer, however, I would recommend using a dataset approach – Thom A Oct 08 '18 at 10:52
I did use this as the solution, mainly because the syntax is more familiar to me and closer to other code used where I work - not because it's necessarily better. it worked though. – HelenS Oct 08 '18 at 10:59

Andrea · Answer 4 · 2018-10-05T13:15:03.867

You can use a simple while loop with stuff:

declare @s       varchar(max) ='dog'
declare @counter int          = 0
declare @len     int          = len(@s)

while @counter < @len - 1
begin 
    set @s = stuff(@s, @len - @counter, 0, '.')
    set @counter = @counter + 1
end
select @s + '.' as result

Since your input is a short string performances should not be a great concern: I tested this solution with a 8000 char string and the result was returned instantly.

Result:

Split string into letters separated by a full stop (SQL)

4 Answers4