Select from this Table

sql, t-sql

Solution

;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.

Problem

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?

Original source