CODEWITHSUNDEEP
sql-server-2008
anki145
anki145

Reputation: 43

SQL Server - Select column where range is given in the column description

ID  Description

51  SY10K40F

18  SY20K40F

12  SY30K40F

28  SY40K40F

34  SY100K40H

35  SY240K40H

36  SY300K40H

I have to select only those id for which description range is 10K to 40K.

Note - Id is primary key and from description we are getting 10k,20k etc.

Upvotes: 4

Views: 103

Answers (3)

valex
valex

Reputation: 24134

If I've got it right:

WHERE 
CAST(SUBSTRING(Description,3,CHARINDEX('K',Description)-3) AS INT)
BETWEEN 10 and 40

SQL Fiddle demo

Assuming your comment to this answer the first position isn't fixed so in this case you should use PATINDEX() function in MSSQL:

WHERE 
    CAST(SUBSTRING(Description,
                    PATINDEX('%[0-9]%',Description), 
                    PATINDEX('%[0-9]K%',Description)-
                    PATINDEX('%[0-9]%',Description)+1)
         AS INT)
      BETWEEN 10 and 40

SQLFiddle demo

Upvotes: 5

Prashant Khadatkar
Prashant Khadatkar

Reputation: 200

Try This:

SELECT t.Id,
       t.desp,
from
(
SELECT [Id]
      ,[Description] as desp
      ,SUBSTRING(SUBSTRING([Description],3,LEN([Description])),0, CHARINDEX('K',SUBSTRING([Description],3,LEN([Description])),0)) as rag      
  FROM [dbo].[testRange]
)t
where CONVERT(INT,t.rag) > 20 AND CONVERT(INT,t.rag) < 40

Upvotes: 0

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

One of the simplest solution is as below,

Select 
  * 
FROM 
  Table1 
WHERE
  Description like '__10K%'
OR
  Description like '__20K%'
OR
  Description like '__30K%'
OR
  Description like '__40K%';

working demo at http://sqlfiddle.com/#!3/b0e49/3

Upvotes: 0

Related Questions