Detect consecutive ranges of numbers

Question

Detect consecutive ranges of numbers

crackbridge

SSC Enthusiast

Points: 185
More actions
June 22, 2011 at 12:10 pm

#389816

In case I have numbered items belonging to a group, I would like to be able
to get rows or a single string saying all found range of numbers in the given
set or group.
For instance, when having:
125
226
227
310
351
352
353
Result should be:
125,226-227,310,351-353
Or
125
226 227
310
351 353
Should also run in 2005,
Thanks in advance.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

winston Smith SSCoach Points: 19500 More actions · Answer 1

your question has no context and is not phrased very well.

If you could explain your question a little clearer, and give some context ( in teh form of an example) you may get more responses. not trying to be nasty, im trying to help you get answers.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

winston Smith (6/22/2011)
your question has no context and is not phrased very well.
If you could explain your question a little clearer, and give some context ( in the form of an example) you may get more responses. not trying to be nasty, im trying to help you get answers.

What winston is saying ... just click on the first link in my signature block and read the article (It also has the T-SQL code) to allow you to post table definition / sample data / expected results from the sample data .. quickly and easily. In that manner you most likely will get a tested answer

In other words help us to help you

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLPirate SSCrazy Points: 2880 More actions · Answer 3

I agree it's not phrased very well, but if I understand correctly then there's a good answer to a similar question over at the msdn forums : http://social.msdn.microsoft.com/Forums/en-CA/transactsql/thread/c097ff31-aa01-4d7f-a1c4-63d3738ed147.

crackbridge SSC Enthusiast Points: 185 More actions · Answer 4

Thank you very much SQL Pirate for your response, it's exactly what I was looking for. I'm sorry previous explanation probably was not that clear to all, but Data was clear enought and perfectly technically comprensible.

According to the evaluation whether this is weird or not, said in msdn, it's very usefull to detect for instance wich primary keys are missing, or to simplify a list of items to be checked and save paper..

Thanks a lot again.

crackbridge SSC Enthusiast Points: 185 More actions · Answer 5

I have been trying to apply the same solution as recommended in the msdn article:

http://social.msdn.microsoft.com/Forums/en-CA/transactsql/thread/c097ff31-aa01-4d7f-a1c4-63d3738ed147

In order to work with alphanumeric islands within a single column, to be able to detect islands or groups of items an put them separated with '-' and put separated isolated values with ','.

For example, in a store with several items having alphanumeric primary key (lets say table Products):

SCREWDRIVER

04738

04739

DRILL400

256

257

258

04740

04741

HAMMER

Return value should be:

256-258,04738-04741,DRILL400,HAMMER,SCREWDRIVER

I could not improve the SQL to do it, could you give some try?

Thanks a lot in advance.

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 6

Try this

DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)

INSERT INTO @Products(ID)

SELECT 'SCREWDRIVER' UNION ALL

SELECT '04738' UNION ALL

SELECT '04739' UNION ALL

SELECT 'DRILL400' UNION ALL

SELECT '256' UNION ALL

SELECT '257' UNION ALL

SELECT '258' UNION ALL

SELECT '04740' UNION ALL

SELECT '04741' UNION ALL

SELECT 'HAMMER';

WITH CTE AS (

SELECT ID,

ROW_NUMBER() OVER(ORDER BY ID) -

CASE WHEN ID LIKE '%[^0-9]%' THEN NULL ELSE CAST(ID AS INT) END AS rnDiff

FROM @Products)

SELECT STUFF((SELECT ','+MIN(ID)+CASE WHEN MAX(ID)>MIN(ID)

THEN '-'+MAX(ID)

ELSE ''

END AS "text()"

FROM CTE

GROUP BY COALESCE(CAST(rnDiff AS VARCHAR(20)),ID)

ORDER BY MIN(ID)

FOR XML PATH('')),1,1,'') As Results;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

crackbridge SSC Enthusiast Points: 185 More actions · Answer 7

Thanks a lot Mark! It works great.

Althought I found out two issues.

First is related to Data:

There are a lot of Alphanumeric PK which are continuous, I'm sorry I did not specified in the first sample data. ie:

DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)

INSERT INTO @Products(ID)

SELECT 'SCREWDRIVER' UNION ALL

SELECT '04738' UNION ALL

SELECT '04739' UNION ALL

SELECT 'DRILL400' UNION ALL

SELECT 'DRILL401' UNION ALL

SELECT 'DRILL402' UNION ALL

SELECT 'DRILL403' UNION ALL

SELECT '256' UNION ALL

SELECT '257' UNION ALL

SELECT '258' UNION ALL

SELECT '04740' UNION ALL

SELECT '04741' UNION ALL

SELECT 'HAMMER';

Output now is:

04738-04741,256-258,DRILL400,DRILL401,DRILL402,DRILL403,HAMMER,SCREWDRIVER

Optimized output should be:

04738-04741,256-258,DRILL400-DRILL403,HAMMER,SCREWDRIVER

The second issue is for long text results the query returns empty.

Thanks again friend, it was great!

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 8

This may break depending on your data

WITH CTE AS (

SELECT ID,

ROW_NUMBER() OVER(ORDER BY ID) -

-- If there are any alphabetic characters following the numeric characters then this will fail

CASE WHEN ID LIKE '%[0-9]%' THEN CAST(SUBSTRING(ID,PATINDEX('%[0-9]%',ID),LEN(ID)) AS INT) END AS rnDiff

FROM @Products)

SELECT STUFF((SELECT ','+MIN(ID)+CASE WHEN MAX(ID)>MIN(ID)

THEN '-'+MAX(ID)

ELSE ''

END AS "text()"

FROM CTE

GROUP BY COALESCE(CAST(rnDiff AS VARCHAR(20)),ID)

ORDER BY MIN(ID)

FOR XML PATH('')),1,1,'') As Results;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

crackbridge SSC Enthusiast Points: 185 More actions · Answer 9

I added a little modification, I eliminated last '%' to indicate I just care final numbers, this works, but it splits groups and create some mess:

DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)

INSERT INTO @Products(ID)

SELECT 'SCREWDRIVER' UNION ALL

SELECT '04737' UNION ALL

SELECT '04738' UNION ALL

SELECT '04739' UNION ALL

SELECT '04740' UNION ALL

SELECT '04741' UNION ALL

SELECT '3DRILL399' UNION ALL

SELECT '3DRILL400' UNION ALL

SELECT '3DRILL401' UNION ALL

SELECT '3DRILL402' UNION ALL

SELECT '3DRILL403' UNION ALL

SELECT '256' UNION ALL

SELECT '257' UNION ALL

SELECT '258' UNION ALL

SELECT 'HAMMER';

WITH CTE AS (

SELECT ID,

ROW_NUMBER() OVER(ORDER BY ID) -

CASE WHEN ID LIKE '%[0-9]' THEN CAST(SUBSTRING(ID,PATINDEX('%[0-9]',ID),LEN(ID)) AS INT) END AS rnDiff

FROM @Products)

SELECT STUFF((SELECT ','+MIN(ID)+CASE WHEN MAX(ID)>MIN(ID)

THEN '-'+MAX(ID)

ELSE ''

END AS "text()"

FROM CTE

GROUP BY COALESCE(CAST(rnDiff AS VARCHAR(20)),ID)

ORDER BY MIN(ID)

FOR XML PATH('')),1,1,'') As Results;

Output is wrong:

04737-04739,04740-04741,256-3DRILL399,3DRILL400-3DRILL403,HAMMER,SCREWDRIVER

Unfortunatelly data is like that, any idea to solve it?

Thanks a lot again.

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 10

You could probably split the primary key into the leading alphanumeric part and trailing numeric part by searching for the first non-numeric character from the end of the string (use REVERSE). Unfortunately I don't have time to do this now, perhaps someone else can step in?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

crackbridge SSC Enthusiast Points: 185 More actions · Answer 11

Good morning Mark, it's DONE!!!.. I'm actually glad! if sometime you need a team, count on me! Please test it when you get some time:

DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)

INSERT INTO @Products(ID)

SELECT 'SCREWDRIVER' UNION ALL

SELECT '04737' UNION ALL

SELECT '04738' UNION ALL

SELECT '04739' UNION ALL

SELECT '3DRILL399' UNION ALL

SELECT '3DRILL400' UNION ALL

SELECT '3DRILL401' UNION ALL

SELECT '3DRILL402' UNION ALL

SELECT '3DRILL403' UNION ALL

SELECT '3DRILL407' UNION ALL

SELECT '9988' UNION ALL

SELECT '04740' UNION ALL

SELECT '04741' UNION ALL

SELECT '256' UNION ALL

SELECT '257' UNION ALL

SELECT '258' UNION ALL

SELECT 'HAMMER';

WITH CTE AS (

SELECT ID,

ROW_NUMBER() OVER(ORDER BY ID) -

CASE WHEN ID LIKE '%[0-9]' THEN CAST(SUBSTRING(ID,LEN(ID)-PATINDEX('%[0-9]%',REVERSE(ID))-1,LEN(ID)) AS INT) END AS rnDiff

FROM @Products)

SELECT STUFF((SELECT ','+MIN(ID)+CASE WHEN MAX(ID)>MIN(ID)

THEN '-'+MAX(ID)

ELSE ''

END AS "text()"

FROM CTE

GROUP BY COALESCE(CAST(rnDiff AS VARCHAR(20)),ID)

ORDER BY MIN(ID)

FOR XML PATH('')),1,1,'') As Results;

Output is:

04737-04741,256-258,3DRILL399-3DRILL403,3DRILL407,9988,HAMMER,SCREWDRIVER

Best Regards,

L.

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 12

crackbridge (6/24/2011)
Good morning Mark, it's DONE!!!.. I'm actually glad! if sometime you need a team, count on me! Please test it when you get some time:
DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO @Products(ID)
SELECT 'SCREWDRIVER' UNION ALL
SELECT '04737' UNION ALL
SELECT '04738' UNION ALL
SELECT '04739' UNION ALL
SELECT '3DRILL399' UNION ALL
SELECT '3DRILL400' UNION ALL
SELECT '3DRILL401' UNION ALL
SELECT '3DRILL402' UNION ALL
SELECT '3DRILL403' UNION ALL
SELECT '3DRILL407' UNION ALL
SELECT '9988' UNION ALL
SELECT '04740' UNION ALL
SELECT '04741' UNION ALL
SELECT '256' UNION ALL
SELECT '257' UNION ALL
SELECT '258' UNION ALL
SELECT 'HAMMER';
WITH CTE AS (
SELECT ID,
ROW_NUMBER() OVER(ORDER BY ID) -
CASE WHEN ID LIKE '%[0-9]' THEN CAST(SUBSTRING(ID,LEN(ID)-PATINDEX('%[0-9]%',REVERSE(ID))-1,LEN(ID)) AS INT) END AS rnDiff
FROM @Products)
SELECT STUFF((SELECT ','+MIN(ID)+CASE WHEN MAX(ID)>MIN(ID)
THEN '-'+MAX(ID)
ELSE ''
END AS "text()"
FROM CTE
GROUP BY COALESCE(CAST(rnDiff AS VARCHAR(20)),ID)
ORDER BY MIN(ID)
FOR XML PATH('')),1,1,'') As Results;
Output is:
04737-04741,256-258,3DRILL399-3DRILL403,3DRILL407,9988,HAMMER,SCREWDRIVER
Best Regards,
L.

Looks good, I think you're sorted now.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

crackbridge SSC Enthusiast Points: 185 More actions · Answer 13

Last Release, tested for new cases, see data.

Requires implementation of function: dbo.IsInteger(ID)

Regards!

DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)

INSERT INTO @Products(ID)

SELECT 'SCREWDRIVER' UNION ALL

SELECT '224537' UNION ALL

SELECT '224538' UNION ALL

SELECT '224539' UNION ALL

SELECT '04737' UNION ALL

SELECT '04738' UNION ALL

SELECT '04739' UNION ALL

SELECT '3DRILL399' UNION ALL

SELECT '3DRILL400' UNION ALL

SELECT '3DRILL401' UNION ALL

SELECT '3DRILL402' UNION ALL

SELECT '3DRILL403' UNION ALL

SELECT '3DRILL409' UNION ALL

SELECT '3DRILL410' UNION ALL

SELECT '3DRILL411' UNION ALL

SELECT '9988' UNION ALL

SELECT '04740' UNION ALL

SELECT '04741' UNION ALL

SELECT '256' UNION ALL

SELECT '257' UNION ALL

SELECT '258' UNION ALL

SELECT 'HAMMER' UNION ALL

SELECT '51T1023-56' UNION ALL

SELECT '51T1023-57' UNION ALL

SELECT '51T1023-58' UNION ALL

SELECT 'FLEX3-6' UNION ALL

SELECT 'FLEX3-7' UNION ALL

SELECT 'FLEX3-8' ;

WITH CTE AS (

SELECT ID,

ROW_NUMBER() OVER(ORDER BY ID) -

CASE

WHEN dbo.IsInteger(ID)=1

THEN CAST(ID AS INT)

WHEN ID LIKE '%[^0-9]%[0-9]' THEN

SUBSTRING(ID,LEN(ID)-PATINDEX('%[^0-9]%',REVERSE(ID))+2,LEN(ID))

END AS rnDiff

FROM @Products)

SELECT STUFF((SELECT ', '+MIN(ID)+CASE WHEN MAX(ID)>MIN(ID)

THEN ' to '+MAX(ID)

ELSE ''

END AS "text()"

FROM CTE

GROUP BY COALESCE(CAST(rnDiff AS VARCHAR(20)),ID)

ORDER BY MIN(ID)

FOR XML PATH('')),1,1,'') As Results;