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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments