select firstname || case when length(middlename) = 0 then '' else ' '
|| middlename end || ' ' || lastname as name from studentmaster
where name like 'abc d%'
In the above statement, I have tried to give the name 'name' to the
combined filed of three columns.
You need an 'end' for the 'case', and you need to use a subquery in
order to reference the field 'name' in your where clause.
Something like this should work:
select * from ( select firstname ||
case when length(middlename) = 0 then '' else ' ' end ||
middlename || ' ' || lastname as name from studentmaster) t
where t.name like 'William J%';
thanks,
bryan
