Selecting a range from mixed alpha-numeric column

Question

Post reply

Selecting a range from mixed alpha-numeric column

Viewing 15 posts - 16 through 30 (of 37 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

select *

from dbo.rangeproof

where left(id, patindex('%[^0-9]%', id)-1) between '123' and '124'

Will get you the results you want, but it's going to be slow on a big table. (I tested it and it does get the ones you want.)

If you can add "left(id, patindex('%[^0-9]%', id)-1)" as a computed column and then index it, you'll get very fast selects. If you can't add it, possibly an indexed view would be better.

(Edit: This, of course, won't work with any codes that begin with something other than a number. Those will have to be handled separately, if there are any.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Forget the cursors, forget the LIKEs... and remember that ISNUMERIC treats "-", and a whole bunch of other characters as valid numeric characters. NEVER use ISNUMBER as IsAllDigits because it's NOT.

The easy way to do this is to make the method available to all stored procs, views, and functions. The only way to do that is to add a column to your table like I did in the following example. Notice that the column can be indexed!

--===================================================================================

-- Create a test table and populate it with data...

-- The "PartRange" column is the solution.

--===================================================================================

CREATE TABLE #jbmTest

(

RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

PartNumber VARCHAR(20),

PartRange AS CAST(LEFT(PartNumber,ISNULL(NULLIF(PATINDEX('%[^0-9]%',PartNumber),0),LEN(PartNumber)+1)-1) AS BIGINT))

CREATE INDEX tIX_jbmTest_PartRange ON #jbmTest (PartRange)

INSERT INTO #jbmTest

(PartNumber)

SELECT '123456789' UNION ALL

SELECT '12345-67-89' UNION ALL

SELECT '12345A67A89' UNION ALL

SELECT '12345 67 89' UNION ALL

SELECT '123456-7-89' UNION ALL

SELECT '123456A7A89' UNION ALL

SELECT '123456 78 9' UNION ALL

SELECT '123' UNION ALL

SELECT '123.456.789' UNION ALL

SELECT '123-456-789' UNION ALL

SELECT '123A456A789' UNION ALL

SELECT '123 456 789' UNION ALL

SELECT 'ABC'

--===== Demo the use of the PartRange column solution

SELECT *

FROM #jbmTest

WHERE PartRange BETWEEN 100 AND 200

SELECT *

FROM #jbmTest

WHERE PartRange = 123

SELECT *

FROM #jbmTest

WHERE PartRange BETWEEN 10000 AND 10100

SELECT *

FROM #jbmTest

WHERE PartRange BETWEEN 12300 AND 12400

SELECT *

FROM #jbmTest

DROP TABLE #jbmTest

Lemme know how that works for you...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TraderSam SSCrazy Points: 2931 More actions · Answer 3

Very nice solution, you guys...that will go in my toolbox!

I, too, would like to hear from the OP to see how well it worked with the existing data.

If it was easy, everybody would be doing it!;)

biscuit SSC Veteran Points: 247 More actions · Answer 4

Thanks for all the suggestions. The only problem with this is that it returns a part number of 123456789 on a range query of 12300 - 12400. In that case, the number does not fall into the range but it is still selected.

I will probably have to add some logic to the application to generate the rangeid as was suggested earlier or I will try the formula mentioned earlier.

Thanks again for all the great suggestions!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

I'm pretty sure that you haven't tried my code... give it a try... it's designed to avoid the very problem you mention.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

biscuit SSC Veteran Points: 247 More actions · Answer 6

Jeff, I did try your code and it worked very nicely. But if you will look at the third resultset, you can see where it returned 123456789 and this is well out of the range selected. I will try again but to make sure but for now it looks like it doesn't solve the problem.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

biscuit (3/17/2008)
Jeff, I did try your code and it worked very nicely. But if you will look at the third resultset, you can see where it returned 123456789 and this is well out of the range selected. I will try again but to make sure but for now it looks like it doesn't solve the problem.

Look again... third result set was born from 12345A67A89 which has the letter "A" in it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

biscuit SSC Veteran Points: 247 More actions · Answer 8

Jeff Moden (3/17/2008)
biscuit (3/17/2008)
Jeff, I did try your code and it worked very nicely. But if you will look at the third resultset, you can see where it returned 123456789 and this is well out of the range selected. I will try again but to make sure but for now it looks like it doesn't solve the problem.
Look again... third result set was born from 12345A67A89 which has the letter "A" in it...

I don't know how I could be getting different results, but on my machine the first record of the third resultset is 123456789. It is also returning 123456 which is also outside of the range requested of 12300-12400

TraderSam SSCrazy Points: 2931 More actions · Answer 9

The queries with Jeff's data seems to be working OK in my environment, but I'm currently working in SQL 2000.

If it was easy, everybody would be doing it!;)

biscuit SSC Veteran Points: 247 More actions · Answer 10

Thats really odd. I am working with 2K5. I copied and pasted the code twice and got the same results. It is including numbers outside of the specified range. IDK maybe my box is possessed.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

I don't have 2k5 at work... I'll have to wait until tonight to find out what's happening. Could you send the output you're getting so we can do a comparison. Also, are you running at least sp2?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 12

I tried this on a SQL 2005 box here and my third resultset is empty.

I get 5 records, 5 records, 0 records, 3 records, and 13 records.

TraderSam SSCrazy Points: 2931 More actions · Answer 13

dfalso (3/17/2008)
I tried this on a SQL 2005 box here and my third resultset is empty.
I get 5 records, 5 records, 0 records, 3 records, and 13 records.

I got the same thing as the above on SQL 2000.

If it was easy, everybody would be doing it!;)

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 14

Trader Sam (3/17/2008)
dfalso (3/17/2008)
I tried this on a SQL 2005 box here and my third resultset is empty.
I get 5 records, 5 records, 0 records, 3 records, and 13 records.
I got the same thing as the above on SQL 2000.

Which looks correct to me. Is anyone not getting this result?

biscuit SSC Veteran Points: 247 More actions · Answer 15

Here is what I get:

(13 row(s) affected)

RowNum PartNumber PartRange

----------- -------------------- --------------------

8 123 123

9 123.456.789 123

10 123-456-789 123

11 123A456A789 123

12 123 456 789 123

(5 row(s) affected)

RowNum PartNumber PartRange

----------- -------------------- --------------------

8 123 123

9 123.456.789 123

10 123-456-789 123

11 123A456A789 123

12 123 456 789 123

(5 row(s) affected)

RowNum PartNumber PartRange

----------- -------------------- --------------------

(0 row(s) affected)

RowNum PartNumber PartRange

----------- -------------------- --------------------

2 12345-67-89 12345

3 12345A67A89 12345

4 12345 67 89 12345

(3 row(s) affected)

RowNum PartNumber PartRange

----------- -------------------- --------------------

1 123456789 123456789

2 12345-67-89 12345

3 12345A67A89 12345

4 12345 67 89 12345

5 123456-7-89 123456

6 123456A7A89 123456

7 123456 78 9 123456

8 123 123

9 123.456.789 123

10 123-456-789 123

11 123A456A789 123

12 123 456 789 123

13 ABC 0

(13 row(s) affected)

I miscounted the number of resultsets... But if you look at the last one, the number is 123456789 which is not in the range requested. I am running 2k5 SP2