0

In my Where Clause I want to return all rows where the 'Code' is from 'A2000' all the way to 'A2999'.

I am using Like 'A2%' for now because I know that this currently returns only values between A2000 and A2999. But this could change and there could be values like A216 or A27 or A2 ... in the future.

Is there a way achieve what I want without listing one thousand consecutive codes [from A2000 to A2999]?

Thanks!

Talay
  • 349
  • 1
  • 5
  • 17
  • 3
    `like 'A2[0-9][0-9][0-9]'`? – GSerg Jul 07 '17 at 15:10
  • Thanks @GSerg! Works perfectly. I've never seen that construct before! Appreciate it. – Talay Jul 07 '17 at 15:15
  • that is very cool, I did not know you could do that :) – Stephen Byrne Jul 07 '17 at 15:41
  • Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. – HABO Jul 07 '17 at 17:42

1 Answers1

-1

It's worth noting that you can you BETWEEN for this kind of problem. 

-- Sample data
WITH E(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(x)),
iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))+999 FROM E a, E b, E c, E d),
sc(someCode) AS (SELECT CONCAT('A', N) FROM iTally UNION ALL SELECT CONCAT('B', N) FROM iTally)
SELECT SomeCode INTO #codes FROM sc;

-- Solution
SELECT someCode
FROM #codes
WHERE someCode BETWEEN 'A2000' AND 'A2999';

Update (20170713):

Sorry for the late update, I must the second comment below. I just read it.

It is correct that my WHERE clause would pick up the value "A22"; it would also pick up "A2X00". That said, if the if the format you are working with looks like this: CDDDD (with C being a character and D being digit) then my solution is correct and SARGable. This is one of the reasons it's best to include DDL when posting a SQL Question. Note this article.

Update #2

Re: the question about how I generated the sample data.

I like explaining this because it invlolves tally tables - one of my favorite SQL topics ans one too view developers have any knowledge of. My sample data coded generates the values A1000, A1001, A1002... though ...B15638, B15639, B15640. This provides a mix of "good" and bad values for you to test my logic against. Tally tables are indispensible. Here's three of my favorite articles on the topic:

The "Numbers" or "Tally" Table: What it is and how it replaces a loop -- Jeff Moden

Tally Tables in T-SQL -- Dwain Camps

Virtual Auxiliary Table of Numbers -- Itzik Ben-Gan

So here's what the code does:

  1. Creates 10 dummy values in the first CTE (E)
  2. Cross Joins the values to themselves three times in the second CTE (iTally) This very quicky generates 10,000 rows (10*10*10*10)

  3. Numbers the rows 1-10000 using ROW_NUMBER (still in the iTally CTE)

  4. Cross joins the numbers 1-10000 to the Values "A" and "B" then concatinates the letters & numbers

  5. Does a SELECT INTO to create a temp table to hold the values.

Notice I don't return the "dummy values" in my final SELECT statement; they only exist so that ROW_NUMBER() can generate the numbers. ROW_NUMBER() requires an OVER (ORDER BY) clause - which can cause a sort in the execution plan and slow my query down. ORDER (BY ) acceptable syntax as far as SQL Server 2005+ is concerned and this circumvents that nasty sort.

On my laptop (an HP with 32gb, 4 2.8ghz CPU - 8 logical CPU, 7200 rpm disk) I can generate the numbers 1 to 1,000,000,000 in 70-90 seconds. This is one of many reasons I love tally tables.

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
  • Thanks for your response. Could you please, if you have the time, offer a short explanation of your code that creates the data for #codes? I'll understand if you don't have the time - but I would like to understand what is going on and how the three CTE's work. I DID run it and it works just fine. I also tried the BETWEEN 'A2000' AND 'A2999' in my Where clause and it works perfectly. Thanks! – Talay Jul 07 '17 at 21:04
  • This code makes no sense in the context of the question. It generates values from A2000 to A10999, which is not what is required, and then selects those of them that are between 2000 and 2999, which only returns expected results because there are no values in `#codes` what match the `between` but are unwanted, like `'A22'`. There is already a [deleted answer](https://stackoverflow.com/a/44974653/11683) that tries the `between` approach. It was deleted because it was correctly noted that `'A22'` is also `BETWEEN 'A2000' AND 'A2999'`, and the very point of the OP's question is to exclude those. – GSerg Jul 08 '17 at 10:28
  • @Talay I'm glad my solution worked for you. I just updated my post to answer your question about what I was doing with the CTEs. – Alan Burstein Jul 14 '17 at 03:04
  • @GSerg the OP did not include any DDL so the we have to use our imagination a little. We don't know if A22 is an acceptable value. Nonetheless, I updated my post accordingly. Regarding my sample data - the sample data was provided to test my solution. The OP can copy/paste/run my code and see that it returns A2345 and doesn't return B1234. How does that not make sense to you? – Alan Burstein Jul 14 '17 at 03:06
  • @AlanBurstein The DDL is not important. The very point of the OP's question is `But this could change and there could be values like A216 or A27 or A2 ... in the future`. They currently don't have A27 in the source data but if it will appear there they don't want it in the results. [Here's a screenshot](https://i.stack.imgur.com/5wVTP.png) of the comments under the deleted question you cannot yet see. – GSerg Jul 14 '17 at 06:47