concantenate

[thorny00]

swampboogie, you gave me the following code for my question and I greatly appreciate but it is not moving the lastname and firstname into the fullname field if instname is blank. What could be causing this?? I take it that the || are a plus sign(+)??

create view x as
select coalesce(instname,lastname||', '||firstname) as fullname
from tableInAWarehouse

 

[swampBoogie]

No, || stands for concatenation in ANSI SQL. Many DBMS have some other symbol this

+ in sql server/sybase
& in Jet SQL
concat in Mysql.

>> if instname is blank >>

You said null in your previous post. coalesce will return the first non null argument.

If instname can be an empty string or blank or null you can write

select case when trim(coalesce(instname,'')) = '' then lastname||', '||firstname else instname end as fullname
from t

Which DBMS are you using?