CODEWITHSUNDEEP
sqloracle-database
Jerry Contreras
Jerry Contreras

Reputation: 35

Why is no data being returned

I am trying to find majors that end with S, but no data is being returned

create table Student
 (sid char(10) primary key, 
  sname varchar(20) not null, 
  gpa float, 
  major char(10), 
  dob DATE);

insert into Student values('111', 'Joe', 3.5 , 'MIS', '01-AUG-2000');
insert into Student values('222', 'Jack', 3.4  , 'MIS', '12-JAN-1999');
insert into Student values('333', 'Jill', 3.2 , 'CS', '15-MAY-1998');
insert into Student values('444', 'Mary', 3.7 , 'CS', '17-DEC-2001');
insert into Student values('555', 'Peter', 3.8 , 'CS', '19-MAR-1999');
insert into Student values('666', 'Pat', 3.9,  'Math', '31-MAY-2000');
insert into Student values('777', 'Tracy', 4.0,  'Math', '18-JUL-1997');

SELECT * FROM STUDENT
WHERE MAJOR LIKE '%S'

This doesn't work on livesql (oracle (PL-SQL) based but works on T-SQL).

This is the error message: 'No data found'

Upvotes: 1

Views: 63

Answers (2)

Littlefoot
Littlefoot

Reputation: 142993

If you can't (or won't) modify datatype to varchar2 (not varchar as Stilgar suggested), then trimming the column might help:

SQL> select * from student
  2  where trim(major) like '%S';

SID        SNAME                       GPA MAJOR      DOB
---------- -------------------- ---------- ---------- --------
111        Joe                         3,5 MIS        01.08.00
222        Jack                        3,4 MIS        12.01.99
333        Jill                        3,2 CS         15.05.98
444        Mary                        3,7 CS         17.12.01
555        Peter                       3,8 CS         19.03.99

Upvotes: 1

Stilgar
Stilgar

Reputation: 461

Your CHAR(10) will always contain 10 characters so it is going to be padded with space characters. Change to VARCHAR2(10) and it will work. I'm assuming Oracle since you didn't specify which database. But VARCHAR(10) should work with T-SQL.

Upvotes: 3

Related Questions