CODEWITHSUNDEEP
sqlsql-serversql-server-2012
obsessiveCookie
obsessiveCookie

Reputation: 1138

SQL Get values between two telephone numbers

Would it be possible to get values in between two phone numbers. e.g.

DECLARE @startrange VARCHAR(20) = '1132282220'  
DECLARE @endrange VARCHAR(20) = '1132282230'

Id imagine you convert these variables to BIGINT .

Desired output:

1132282220 1132282221 1132282222 1132282223... 1132282230

Upvotes: 0

Views: 59

Answers (1)

fabulaspb
fabulaspb

Reputation: 1263

If you want to get values by table rows you can use next SQL code

DECLARE @startrange VARCHAR(20),
        @endrange VARCHAR(20),
        @startrange_ BIGINT,
        @endrange_ BIGINT

DECLARE @result_tbl TABLE(VALUE BIGINT)

SET @startrange = '1132282220'
SET @endrange = '1132282230'
SET @startrange_ = CAST(@startrange AS BIGINT)
SET @endrange_ = CAST(@endrange AS BIGINT)

WHILE @startrange_ <= @endrange_
BEGIN
    INSERT INTO @result_tbl VALUES(@startrange_)

    SET @startrange_ = @startrange_ + 1
END

SELECT * FROM @result_tbl AS RT

If you want to get values in one row you can use code like this

DECLARE @startrange VARCHAR(20),
        @endrange VARCHAR(20),
        @startrange_ BIGINT,
        @endrange_ BIGINT

DECLARE @output_value VARCHAR(MAX)

SET @startrange = '1132282220'
SET @endrange = '1132282230'
SET @startrange_ = CAST(@startrange AS BIGINT)
SET @endrange_ = CAST(@endrange AS BIGINT)

WHILE @startrange_ <= @endrange_
BEGIN
    SET @output_value = ISNULL(@output_value, '') 
                            + CAST(@startrange_ AS VARCHAR) + ' ' 

    SET @startrange_ = @startrange_ + 1
END

SELECT RTRIM(@output_value)

Also I've written recursive CTE solution to solve this task

DECLARE @startrange VARCHAR(20),
        @endrange VARCHAR(20),
        @startrange_ BIGINT,
        @endrange_ BIGINT

DECLARE @output_value VARCHAR(MAX)

SET @startrange = '1132282220'
SET @endrange = '1132282230'
SET @startrange_ = CAST(@startrange AS BIGINT)
SET @endrange_ = CAST(@endrange AS BIGINT)

;WITH Result AS(
SELECT @startrange_ AS startrange
UNION ALL
SELECT startrange + 1 FROM Result
WHERE startrange < @endrange_)

SELECT @output_value = ISNULL(@output_value, '') 
                        + CAST(startrange AS VARCHAR) + ' '
FROM Result

SELECT RTRIM(@output_value)

Upvotes: 1

Related Questions