SQL Server - Where condition usage

Question

I want to add condition to below select sql code.

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
order by drivename

The result is ;

drivename   capacity(GB)    freespace(GB) 
C:\            120                36 
D:\            100                 7

I want to add like this : 'freespace(GB) > 10'

How can i add this condition?

score 3 · Answer 1 · edited May 23 '17 at 11:54

Use CTE or subquery:

;WITH cte as
(
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
)
SELECT *
FROM cte
WHERE [freespace(GB)] > 10
order by drivename;

Second this is classic example of not understanding Logical Query Processing

Abhishek · Answer 2 · 2015-09-21T09:24:40.350

2

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
AND round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0) > 10
order by drivename

If you want to display only the C:\ information the above query would do.

edited Sep 21 '15 at 09:24

answered Sep 21 '15 at 09:12

Abhishek

2,482
1
21
32

1

@sezercan Try using `AND round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0) > 10` but your code will be duplicated and uglier – Lukasz Szozda Sep 21 '15 at 09:22
1

@Abhishek You use only part of expression, calculating freesize has 2 lines – Lukasz Szozda Sep 21 '15 at 09:22

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Multiple ways to do this.. Temp table and CTE may seems like the same but try to understand difference between them from here.

By using Temporary table

select * into ##t from 
    (select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename,
    round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
    (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)',
    round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
    (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
    from #output
    where line like '[A-Z][:]%') as T

Select * from ##t where [freespace(GB)] > 10 order by drivename

By Using CTE

;WITH cte as
(
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
)

SELECT * FROM cte WHERE [freespace(GB)] > 10 order by drivename;

By directly using condition in where clause

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename,
round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)',
round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
And (round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)) > 10
order by drivename

SQL Server - Where condition usage

3 Answers3