SQL Select Concat between 2 numbers

Question

I work with SQL Server 2012 and need a concatenate between 2 different columns.

eg:

3 and 7 = 34567
or 1 and 4 = 1234
or 2 and 2 = 2

When I use the Concat Function, I am just able to Concate the first and the last number. But I need the numbers between, too.

score 3 · Answer 1 · answered Aug 12 '15 at 09:52

3

Try this query. Here firstcolumn =3 and secondcolumn=7

SELECT t.Id, 
       ,STUFF((SELECT '' + CAST( n AS VARCHAR(50)) [text()]
         FROM (SELECT DISTINCT n = number 
                FROM master..[spt_values] 
                WHERE number >= firstcolumn AND number <= secondcolumn
                )a
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,0,'') List_Output
FROM tablename t

answered Aug 12 '15 at 09:52

Mukesh Kalgude

4,814
2
17
32

Yes, i have missed the part that he wants to concatenate the digits to a single varchar. – Tim Schmelter Aug 12 '15 at 10:02
Bit late, but there is a simple way to do it using `substring()` function. – Kaf Aug 12 '15 at 10:17

score 2 · Answer 2 · answered Aug 12 '15 at 09:35

There are multiple ways to generate sequences in sql-server. Here is a simple that doesn't need a number-table:

WITH Numbers AS
(
    SELECT TOP (2000) n = ROW_NUMBER() OVER (ORDER BY object_id) 
    FROM sys.all_objects ORDER BY n
)
SELECT n FROM Numbers 
WHERE n BETWEEN 3 AND 7

score 1 · Answer 3 · answered Aug 12 '15 at 09:45

Here's a recursive query that will go from start to end recursively and generate the string you want or an INTEGER value:

DECLARE @start  INT = 3 
DECLARE @end    INT = 7
DECLARE @int_value  INT = 0
DECLARE @str_value  VARCHAR(100) = '';

WITH rec AS (
    SELECT @start AS val
    UNION ALL
    SELECT  val + 1
    FROM    rec 
    WHERE   val < @end
)
SELECT  @str_value = CONCAT(@str_value, val), 
        @int_value = @int_value * 10 + val
FROM    rec

SELECT  @str_value, @int_value

score 0 · Answer 4 · answered Aug 12 '15 at 09:53

This is Itzik's style

declare @values varchar(100)='', @from int, @to int
select @from=3, @to=7
;WITH 
   n0 AS (SELECT 0 AS number UNION ALL SELECT 0), 
   n1 AS (SELECT 0 AS number FROM n0 AS a CROSS JOIN n0 AS b),
   n2 AS (SELECT 0 AS number FROM n1 AS a CROSS JOIN n1 AS b),
   n3 AS (SELECT 0 AS number FROM n2 AS a CROSS JOIN n2 AS b)

   select @values=@values+ltrim(sno) from 
   (select row_number() over (order by number) as sno from n3) as t
   where sno between @from and @to 
   select @values as [values]

Kaf · Answer 5 · 2015-08-12T10:58:40.450

Try this using SUBSTRING() function(Fiddle example):

--Declare sample table
DECLARE @T TABLE (id int identity, numCol1 int, numCol2 int)

--Add some values
INSERT @T (numCol1, numCol2)
VALUES (3, 7), (1, 4), (2, 2)

--Actual Query
SELECT *,  SUBSTRING('123456789', numCol1,  numCol2 - numCol1  + 1) Number
FROM @T

Above query works only with single digit numbers. Modified version (below) to work with numbers like 34, 78

SELECT *,  
       SUBSTRING('123456789', CONVERT(int, LEFT(numCol1,1)), 
          CONVERT(int, RIGHT(numCol2, 1)) - convert(int, LEFT(numCol1,1))  + 1) YourNumber
FROM @T

Note: Number column is returning a string, can be converted to an int using convert() function

score 0 · Answer 6 · answered Aug 12 '15 at 10:21

Thank you for the Answer. I am going to use the answer from @Mukesh Kalgude. So, my full query is the follow:

select 
DayFrom,DayTo,
STUFF((SELECT TOP 7'' + CAST( n AS VARCHAR(50)) [text()]
         FROM (SELECT DISTINCT n = number 
                FROM master..[spt_values] 
                WHERE number >= DayFrom AND DayTo <= 7
                )a
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,0,'') List_Output
from SwitchProfilePairs

The result is dayFrom = 1 day To = 1 But the List_Output is 1234567

SQL Select Concat between 2 numbers

6 Answers6