Select from this Table

Question

I have a table in SQL Server called tbl_spe that contains some specifications of a computer like CPU, RAM,... I want to select all rows with this condition: all rows that their RAM are less than 1GB We can simply write this query:

Select * from tbl_spe where RAM <= 1

Or something like that, but the problem is about table's data :

ID  RAM

159 2GB DDR2
160 256MB DDR
161 3GB DDR2
162 512MB DDR

How can I extract number's part of the RAM field? and tell to query select all row that their RAM are less than 1GB?

Answer 1

;WITH CTE AS
(
SELECT *,
       CONVERT(varchar(100), LEFT(RAM, PATINDEX('%[a-z]%', RAM) - 1)) AS RAMValue,
       RIGHT(CONVERT(varchar(100), LEFT(RAM, PATINDEX('%[ ]%', RAM) - 1)), 2) AS RAMFactor
FROM   tbl
)
SELECT *
FROM   CTE
WHERE  RAMFactor = 'MB'

Here is an example in SQLFiddle

If you wanted to have less than 2GB then change the WHERE clause to this:

WHERE  RAMFactor = 'MB'
   OR  (RAMFactor = 'GB' AND RAMValue < 2)

The first check will get all records that are measured in 'MB', therefore less than a 'GB'. The second check will get all records that are measured in 'GB' and have a value of less than 2, therefore 'GB' values less than 2GB.

Answer 2

SELECT * FROM tbl_spe
WHERE CAST( SUBSTRING(RAM,0,PATINDEX('%[a-zA-Z]%',RAM)) AS INT ) <=1

PATINDEX('%[a-zA-Z]%',RAM) finds the position of first alphabet and SUBSTRING function extracts number from string.

EDIT : Below query takes into consideration RAM in MB and GB . Otherwise above query incorrectly returns 256MB Ram greater than 1GB

 SELECT * FROM tbl_spe
 WHERE 
     ( CAST( SUBSTRING(RAM,0,PATINDEX('%[a-zA-Z]%',RAM)) AS INT ) <=1 AND
       RAM like '%GB%' )
      OR
     ( CAST( SUBSTRING(RAM,0,PATINDEX('%[a-zA-Z]%',RAM)) AS INT ) <=1000 AND
       RAM like '%MB%' )