Skip to main content

Simple Trick Using ISNULL

Simple Trick Using ISNULL:

Step-1: Create Sample Student Table.

CREATE TABLE Student
(
Id INT IDENTITY(1,1) PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
LivingLocation VARCHAR(100) NULL
)
Step-2 :Insert Some Sample Data

INSERT INTO Student
SELECT 'Rakesh','Hyderabad'
UNION ALL
SELECT 'Raju','Delhi'
UNION ALL
SELECT 'Madhu','Hyderabad'
UNION ALL
SELECT 'Naresh',NULL
UNION ALL
SELECT 'Venaktesh','Chennai'

Step-3 :Select Data from Student Table

SELECT * FROM Student









If we observe above data of student table we have student id 4 record LivingLocation is NULL.

Step -4 :Select Data from Student Table With Where Clause.

SELECT * FROM Student WHERE LivingLocation='Hyderabad'






In the above query we are included the where clause with LivingLocation is 'Hyderabad'

SELECT * FROM Student WHERE LivingLocation<>'Hyderabad'






In the above query we want data from Student Table whose LivingLocation is not equal to the 'Hyderabad' , but it is not selected the data whose id is '4' ,because of the id of '4' LivingLocation is NULL , but we need that record also in this situation we can we ISNULL function.

SELECT * FROM Student WHERE ISNULL(LivingLocation,'')<>'Hyderabad'




Labels:

Comments

  1. UnknownJanuary 28, 2015 at 4:29 AM

    Good one !!

    ReplyDelete
  2. UnknownJanuary 29, 2015 at 6:15 PM

    INSERT INTO Student
    VALUES('SHABARINADH','VIJAYAWADA')
    ,VALUES('Raju','Delhi')
    ,VALUES('Madhu','Hyderabad')
    ,VALUES('Naresh',NULL)
    ,VALUES('Venaktesh','Chennai')


    What about this?

    ReplyDelete
  3. AnonymousJanuary 29, 2015 at 11:05 PM

    INSERT INTO Student (StudentName,LivingLocation)
    VALUES('SHABARINADH','VIJAYAWADA'),
    ('Raju','Delhi'),
    ('Madhu','Hyderabad'),
    ('Naresh',NULL),
    ('Venaktesh','Chennai')

    ReplyDelete

Post a Comment

Popular posts from this blog

Rank Functions in SQL SERVER

1 . ROW_NUMBER () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition. 2. RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set. 3. DENSE_RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking. 4. NTILE ( INTEGER_EXPRESSION ) OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups. Examples: --create Employee table create table Employee (                 EmpId int identity ( 1 , 1 ) primary key ,              ...
1 comment
Read more

Difference between LEN and DATALENGTH

LEN: LEN function returns the number of characters in a variable .it also removes the trailing spaces and then then return the length. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT LEN ( @Name ) as [len] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= 'rakesh ' SELECT LEN ( @Name ) as [len] Output: When we observe above variable assigned 'rakesh ' string after that added 3 spaces . Len function removes trailing spaces not leading spaces. DATALENGTH : DATALENG function returns the number of bytes occupy in a variable .it also considered the spaces also. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT DATALENGTH ( @Name ) as [DataLength] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= ' rakesh ' SELECT DATALENGTH ( @Name ) as [DataLength] Output: In above example before ' r ' and after ...
Post a Comment
Read more