user3515453 user3515453 - 4 years ago 119
SQL Question

In a stored procedure if value is empty space or Nil change that value to others :SQL

I have this stored procedure and I want to replace the column value in case if its empty string or Nil as written than those values should be changed to "others". I tried the replace function, but that did not fit with there. The values I get them in a dropdownlist

if(@Type='2')
Begin
select distinct APlan.FlightNumber
from tblArrivalPlan APlan
where (Status=5 or Status=7 or Status=68)

--select replace (APlan.FlightNumber,'004','JackSparrow')
--from tblArrivalPlan APlan
end

Answer Source

You could use CASE to change '', 'Nil', NULL => 'others', rest values will have their original values:

if(@Type='2')
Begin
  select distinct 
     CASE WHEN APlan.FlightNumber IS NULL 
               OR APlan.FlightNumber IN ('', 'Nil') THEN 'others'
          ELSE APlan.FlightNumber
     END AS FlightNumber
  from tblArrivalPlan APlan
  where (Status=5 or Status=7 or Status=68)
end
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download