March 10, 2009 at 11:47 pm
Greetings people,
I have racked my brain trying to come up with a simple query that will return a set strings that follow each other sequentially. What I mean is as follows:
The simple statement SELECT '00001' returns the string '00001'. I am now trying to return a set of string say between '00001' and '00005' something along the lines of SELECT BETWEEN '00001' AND '00005'
Any ideas?
Pete.
March 11, 2009 at 7:19 am
The Between will actually work. If the character string is always a padded numeric you can also do an Explicit conversion to int and then the between.
This sets up a test:
DECLARE @test TABLE (string CHAR(6)) ;
WITH cteTally
AS (SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY [name]) AS n
FROM
sys.all_columns AS AC)
INSERT INTO
@test (string)
SELECT TOP 100
RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(4), n), 6)
FROM
cteTally;
SELECT
*
FROM
@test
WHERE
string BETWEEN '000001' AND '000005';
SELECT
*
FROM
@test
WHERE
CONVERT(INT, string) BETWEEN 1 AND 5;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2009 at 7:45 am
Thanks Jack for you contribution.
Only one problem though...this solution assumes that the string are already present in a table. I was thinking of a scenario where they dont exist to begin with and you are trying to generate them for example when you want to insert a sequential list of serial numbers into a table.
I was thinking of creating say a stored procedure whereby I pass two parameters to it. A start serial number and an end serial number eg '210000001' and '210000009' and the procedure will insert the range for me
March 11, 2009 at 8:11 am
Again, this assumes that you are just left padding integers.
DECLARE @test TABLE (string CHAR(9)) ;
DECLARE @start INT, @end INT
- this is your range
SELECT @start = 210000001, @end = 210000009
-- this builds a million row tally table (well CTE)
;WITH cteTally
AS (SELECT TOP 1000000
ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
FROM
sys.all_columns AS AC CROSS JOIN
sys.all_columns AS AC2 )
INSERT INTO
@test (string)
SELECT
RIGHT(REPLICATE('0', 8) + CONVERT(VARCHAR(9), (n + @start -1)), 9)
FROM
cteTally
WHERE
-- need the - 1 because cteTally starts with 1
(n + @start -1) BETWEEN @start and @end
SELECT
*
FROM
@test
If you already have a tally/numbers table you can use that as long as the range is less than the number of rows in your tally table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply