-1

I'm working on SQL 2008 and have a table with 1000's of codes in it - a sample would be:

D37
D37.0
D38
D38.0
D39
D39.0
D3A
D3A.0
D40

What I need to do is select the values between D37 and D40. However, I do not want the D3A values (or D3B or D3C for that matter). I have tried the following:

SELECT Code
FROM Table
WHERE Code BETWEEN 'D37' AND 'D40'

However, this get all the codes listed above, including the D3A codes.

Is there a way to exclude the codes that do not fall in the 37-40 range?

Code Different
  • 90,614
  • 16
  • 144
  • 163
user2271146
  • 35
  • 1
  • 4
  • 'D3A' is between 'D37' and 'D40', it is usual it appears in results. – dani herrera Jun 02 '15 at 15:33
  • Perhaps you could explain why you think it shouldn't be between D37 and D40? It should be alphabetically and hexadecimally... – Matt Fellows Jun 02 '15 at 15:34
  • @MattFellows, hexadecimally? no one talks about hexadecimale in question. Just about varchar. – dani herrera Jun 02 '15 at 15:35
  • I guess I'm looking more at the numerical portion of the code - the 37 and don't consider A to be between 37 and 40. – user2271146 Jun 02 '15 at 15:37
  • Then you should talk about this condition in your question. Perhaps `and isnumeric( left(code,2))=1 and len(code)=3` in your `where` clause can helps. – dani herrera Jun 02 '15 at 15:40
  • This is yet another example of why you only store a single item per column. A better design would have split the letter, number and decimal portion of this "code" into three columns. You could display all three together, yet query each part separately. – KM. Jun 02 '15 at 17:01
  • @danihp No - indeed no one does mention Hexadecimal - but I was attempting to think of a reason that the OP would not think D3A should not be between D37 and D40. Is there something wrong with this? Are VARCHAR columns inherently unable to store hexadecimal values? – Matt Fellows Jun 03 '15 at 12:32
  • @MattFellows, sorry, my mistake, I thinked yours first comment was I reply to my own. Be happy! ;) – dani herrera Jun 03 '15 at 13:04

2 Answers2

1

Assuming that the single-letter convention is followed throughout, and there aren't any more weird characters than shown in the data, you can do this:

WITH cte AS (
    [MyColumn]
  , SELECT SUBSTRING([MyColumn],2,LEN([MyColumn)-1) AS Code
  FROM MyTable
  WHERE ISNUMERIC(SUBSTRING([MyColumn],2,LEN([MyColumn)-1)=1
)
SELECT [MyColumn]
FROM cte
WHERE CAST(Code AS float) BETWEEN 37 AND 40
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

It's messy, but this should do what you are asking.

SELECT code
from Mytable
where
ISNUMERIC( SUBSTRING(code, 2, (len(code)) ) ) > 0
and convert(float, SUBSTRING(code, 2, (len(code))) ) between 37 and 40

MartianCodeHound
  • 355
  • 1
  • 8