T-SQL return every other character from string

Question

How do you return every other character from a string from a specific starting position?

Example: starting at position 1

Result:

Starting at position 2

Result:

score 3 · Answer 1 · answered Apr 23 '18 at 19:18

Using a numbers table is usually the best way to avoid loops in SQL. If you don't already have a numbers table, you should go read Jeff Moden's The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

To create a numbers table, you can use the following script:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1       
    CROSS JOIN sys.objects s2 
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Now that you have a numbers table, you can use it to select the specific chars from your string like this:

DECLARE @s varchar(20) = '1050231',
        @Start int = 1


SELECT Substring(@s, Number, 1)
FROM Numbers
WHERE Number >= @Start
AND (Number - @Start) % 2 = 0
AND Number <= DATALENGTH(@s)

score 2 · Answer 2 · answered Apr 23 '18 at 19:14

2

One method uses a recursive CTE:

with cte as (
      select @pos as pos, @str as str
      union all
      select pos + 2, str
      from cte
      where pos + 2 <= len(@str)
     )
select substring(str, pos, 1)
from cte;

Here is a rextester.

answered Apr 23 '18 at 19:14

Gordon Linoff

1,242,037
58
646
786

score 2 · Accepted Answer · answered Apr 23 '18 at 19:54

2

Late answer, but here's yet another option

Example

Declare @S varchar(max) = '1050231'
Declare @P int =1

Select NewValue = (Select substring(substring(@S,@P,len(@S)),N,1)
                    From  (Select Top (len(@S)-@P+1) N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1) A
                    Where N%2=1
                    For XML Path('')
                   )

Returns

NewValue
1521

answered Apr 23 '18 at 19:54

John Cappelletti

79,615
7
44
66

This option worked best for my situation. I am not able to create a numbers table as I'm using a read-only account. It was easy to incorporate the subselect as a calculated field in an existing query. – Bobby Zimmerman Apr 23 '18 at 20:46
@BobbyZimmerman Happy to help. BTW - You should talk to your DBA about getting a numbers table. – John Cappelletti Apr 23 '18 at 20:48
@JohnCappelletti Please note that [master..spt_values is undocumented](https://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-master-spt-values-and-what-are-the-meanings), and though it seems unlikely to disappear some day, it still can. You can replace that with a stacked cte (Itzik Ben Gan style - you know - `WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), E02(N) AS (SELECT 1 FROM E00 a, E00 b),...`). – Zohar Peled Apr 23 '18 at 21:07
@ZoharPeled Yes, and I use that approach as well as demonstrated in many of my solutions. That said, you can replace master..spt with ANY table of appropriate size. – John Cappelletti Apr 23 '18 at 21:11
1

True, the `row_number() over(order by (select null))` makes the actual table irrelevant. I didn't think about that. +1. – Zohar Peled Apr 23 '18 at 21:15

score 1 · Answer 4 · answered Apr 23 '18 at 19:16

1

The ugly way--a while loop, since Gordon gave the recursive CTE approach.

declare @string varchar(64) = 1050231
declare @start int = 1
declare @result varchar(64) = ''


set @result = @result + substring(@string,@start,1)

while @start < len(@string)
begin
    set @start = @start + 2
    select @result = @result + substring(@string,@start,1)
end

select @result

answered Apr 23 '18 at 19:16

S3S

24,809
5
26
45

1

Not only ugly, but also a nasty RBAR approach. I wouldn't recommend that at all. – Zohar Peled Apr 23 '18 at 19:19
I was beat to the RCTE approach which is what I would use @ZoharPeled – S3S Apr 23 '18 at 19:19
1

Well, then, you should read Jeff Moden's [Hidden RBAR: Counting with Recursive CTE's](http://www.sqlservercentral.com/articles/T-SQL/74118/) – Zohar Peled Apr 23 '18 at 19:23
1

I have read it before, @ZoharPeled, and while it's not always a great performer, it can be great for some things. This question of the OP could be better handled outside of the DB as well, perhaps. I didn't assume the OP was going to do this on N number of rows. Maybe they were parsing a variable from an application where I think creating an additional table isn't worth it. Just *a* way is all. Best depends on the situation. – S3S Apr 23 '18 at 19:26
I totally agree that no approach is best for all circumstances. I also use recursive cte's a lot for local stuff on test databases, but I think that for production databases it's better to use the tally table approach. – Zohar Peled Apr 23 '18 at 19:32
Huge fan of tally tables for sure @ZoharPeled , and I keep Jeff's on an Admin Tools database deployed on ally instances along with Brent Ozar's scripts, nuggets from Aaron Bertrand, Adam Mechanic, all the greats that I know you also follow. – S3S Apr 23 '18 at 19:37

score 0 · Answer 5 · answered Apr 23 '18 at 20:06

You could use STUFF:

declare @i VARCHAR(20) = '1050231';

select @i = IIF(LEN(@i) >= sub.s, STUFF(@i,sub.s,1,''),@i)
FROM(SELECT 1 s UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) sub;
      -- any tally table

SELECT @i;
-- 003

declare @i VARCHAR(20) = '1050231';

select @i = IIF(LEN(@i) > sub.s, STUFF(@i,sub.s+1,1,''),@i)
FROM(SELECT 1 s UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) sub;

SELECT @i;
-- 1521

DBFiddle Demo

T-SQL return every other character from string

5 Answers5