Query to Skip rows if Column value is NULL in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

I have table name with tblItems and table structure shows below

itId itemName 
1 Apple
2 pencil
3 Mango
4 pencil
5 Banana
6
7 Peach
8 Stawbery

I wanted is terminate the statement and return the result if there is any blank cell.

this is my expected out put

3 Mango
4 pencil
5 Banana

 

Posted 3 months ago

Refer below query.

SQL

DECLARE @Test AS TABLE(itId INT,itemName VARCHAR(20))
INSERT INTO @Test VALUES(1,'Apple')
INSERT INTO @Test VALUES(2,'pencil')
INSERT INTO @Test VALUES(3,'Mango')
INSERT INTO @Test VALUES(4,'pencil')
INSERT INTO @Test VALUES(5,'Banana')
INSERT INTO @Test VALUES(6,'')
INSERT INTO @Test VALUES(7,'Peach')
INSERT INTO @Test VALUES(8,'Stawbery')

DECLARE @Min INT,@Max INT
SET @Min = 3
SET @Max = ((SELECT itId FROM @Test WHERE ISNULL(itemName,'') = '') - 1)
SET @Max = COALESCE(NULLIF(@Max,''), @Max, @Min + 6)
SELECT TOP 6 * FROM @Test WHERE itId BETWEEN @Min AND @Max

Output

itIditemName
3 Mango
4 pencil
5 Banana

Posted 3 months ago

Hi satabeach,

Refer below query.

SQL

DECLARE @Test AS TABLE(itId INT,itemName VARCHAR(20))
INSERT INTO @Test VALUES(1,'Apple')
INSERT INTO @Test VALUES(2,'pencil')
INSERT INTO @Test VALUES(3,'Mango')
INSERT INTO @Test VALUES(4,'pencil')
INSERT INTO @Test VALUES(5,'Banana')
INSERT INTO @Test VALUES(6,'')
INSERT INTO @Test VALUES(7,'Peach')
INSERT INTO @Test VALUES(8,'Stawbery')
SELECT * FROM @Test WHERE itId BETWEEN 3 AND 6 AND ISNULL(itemName,'') <> ''

Output

itIditemName
3 Mango
4 pencil
5 Banana