Geek asks and answers

Choose from this table

I have a table in SQL Server called tbl_spethat contains some computer specifications, such as CPU, RAM,... I want to select all rows with this condition: all rows in which their RAM is less than 1 GB We can simply write this query:

Select * from tbl_spe where RAM <= 1

Or something like that, but the problem is in the table data:

ID  RAM

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

How to extract the part number of the RAM field? and tell me to request to select all the lines, that their RAM is less than 1 GB?

+5
source share
6 answers
;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 want to have less than 2 GB, change the WHERE clause to the following:

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

, "", , "". , "GB" 2, "GB" 2 .

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

PATINDEX('%[a-zA-Z]%',RAM) , SUBSTRING .

: . 256MB Ram 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%' )
+4

() @XN16 CASE WHERE, .

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

CASE KB GB KB.....

SQLFIDDLE

+4

Well, I went over to the same problem, so I created my own function for this (of course, I got some help from my friends on 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
+1
source 
SELECT * 
FROM tbl_spe
WHERE RAM LIKE '[1-9]GB%'
OR RAM LIKE '___MB%'
0
source

Two steps:

1) Run a user-defined function to simply extract numerical values ​​from a column RAM.

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 in the second step just use the function

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

SELECT * 
FROM CTE 
WHERE Nums <= 1
0
source

More articles:


All Articles