CODEWITHSUNDEEP
sqlt-sql
Sirwan Afifi
Sirwan Afifi

Reputation: 10824

Select from this Table

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?

Upvotes: 5

Views: 336

Answers (6)

XN16
XN16

Reputation: 5869

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

Upvotes: 5

Akash KC
Akash KC

Reputation: 16310

I have taken value(size) extraction part of RAM field using @XN16 query and used CASE condition in WHERE clause to apply the condition according to the size given.

SELECT * FROM tbl_spe
WHERE 1 <=
   CASE
     WHEN CHARINDEX('MB',SUBSTRING(RAM, 1, CHARINDEX(' ', RAM) - 1)) > 0
     THEN CAST(CONVERT(varchar(100), LEFT(RAM, PATINDEX('%[a-z]%', RAM) - 1)) AS FLOAT) * 0.001 
     WHEN CHARINDEX('GB',SUBSTRING(RAM, 1, CHARINDEX(' ', RAM) - 1)) > 0
     THEN CAST(CONVERT(varchar(100), LEFT(RAM, PATINDEX('%[a-z]%', RAM) - 1)) AS FLOAT)
   END

In above CASE condition, you can add the case for KB and add the logic to get GB from given size of KB.....

Check out SQLFIDDLE

Upvotes: 4

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

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%' )

Upvotes: 4

Salah Mousa Basha
Salah Mousa Basha

Reputation: 45

Well I have crossed the same problem so I created my own function for this (of course I got some help from my friends in the internet) this is the function:

    CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''

WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END

RETURN @IntNumbers
END
GO

Upvotes: 1

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

Two steps:

1) Run a custom function to just extract the numeric values from RAM column.

CREATE Function [dbo].[RemoveNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @NumRange as varchar(50) = '%[0-9]%'
    While PatIndex(@NumRange, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1, '')

    Return @Temp
End

2) And second step, simply use the function

WITH CTE
AS
(
   SELECT *,
          RemoveNumericCharacters(RAM) AS Nums
   FROM tbl_spe
)

SELECT * 
FROM CTE 
WHERE Nums <= 1

Upvotes: 0

Inus C
Inus C

Reputation: 1551

SELECT * 
FROM tbl_spe
WHERE RAM LIKE '[1-9]GB%'
OR RAM LIKE '___MB%'

Upvotes: 0

Related Questions