sql - MySQL CONCAT returns NULL if any field contain NULL -

-

i have following data in table "devices" 

affiliate_name  affiliate_location  model     ip             os_type    os_version   cs1             inter               dell     10.125.103.25   linux      fedora   cs2             inter               dell     10.125.103.26   linux      fedora   cs3             inter               dell     10.125.103.27   null       null     cs4             inter               dell     10.125.103.28   null       null

i executed below query 

select concat(`affiliate_name`,'-',`model`,'-',`ip`,'-',`os_type`,'-',`os_version`) device_name devices

it returns result given below

cs1-dell-10.125.103.25-linux-fedora cs2-dell-10.125.103.26-linux-fedora (null) (null)

how come out of should ignore null , result should be

cs1-dell-10.125.103.25-linux-fedora cs2-dell-10.125.103.26-linux-fedora cs3-dell-10.125.103.27- cs4-dell-10.125.103.28-

convert null values empty string wrapping in coalesce

select concat(coalesce(`affiliate_name`,''),'-',coalesce(`model`,''),'-',coalesce(`ip`,''),'-',coalesce(`os_type`,''),'-',coalesce(`os_version`,'')) device_name devices

Comments

Post a Comment

Popular posts from this blog

python - cx_oracle unable to find Oracle Client -

-
i have installd python 2.7.3 on linux 64 bit machine. have oracle 11g client(64bit) installed. , set oracle_home, path, ld_library_path, , installed cx_oracle 5.1.2 version python 2.7 & oracle 11g. ldd command on cx_oracle unable find libclntsh.so.11.1. i tried creating symlinks libclntsh.so.11.1 under /usr/lib64, updated oracle.conf file under /etc/ld.so.conf.d/. tried possible solutions have been discussed on issue on forums, no luck. please let me know missing. the issue me installed python, cx_oracle root oracle client installation done "oracle" user. got own oracle installation , fixed issue. later ran pyunicodeucs4_decodeutf16 issues python , had install python —enable-unicode=ucs4 option
Read more

Delphi XE2 Indy10 udp client-server interchange using SendBuffer-ReceiveBuffer -

-
i use delphi xe2 , indy10 udp protocol. can't receive server echo on client side if use receivebuffer method. got "socket error # 10040" although send small echo message server client. console application illustrate problem below. in advance. program project1; {$apptype console} {$r *.res} uses system.sysutils, idglobal, idbasecomponent, idcomponent, idsockethandle, idudpclient, idudpserver, idudpbase, idstack; type tudp_serv = class(tidudpserver) procedure udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); end; var udpserver: tudp_serv; udpcl: tidudpclient; bsnd, brcv: tbytes; s: string; k: integer; //============================================================================== procedure tudp_serv.udpsvudpread(athread: tidudplistenerthread; adata: tidbytes; abinding: tidsockethandle); begin writeln(' server read: ' + tohex(adata, length(adata))); abinding sendto(peerip, peerport, adat...
Read more

Qt ActiveX WMI QAxBase::dynamicCallHelper: ItemIndex(int): No such property in -

-
i new windows programming. here goes code objiwbemlocator = new qaxobject("wbemscripting.swbemlocator"); objwmiservice = objiwbemlocator->querysubobject("connectserver(qstring&,qstring&)",qstring("."),qstring("root\\cimv2")); qaxobject* returnlist = objwmiservice->querysubobject("execquery(qstring&)", qstring("select * %1").arg(domain)); qaxobject* result = returnlist->querysubobject("itemindex(int)", 0); i getting error on runtime qt activex wmi qaxbase::dynamiccallhelper: itemindex(int): no such property in but itemindex method exists msdn says the itemindex method not work collections not contain swbemobjects, such swbemmethodset, swbemnamedvalueset, swbemprivilegeset, swbempropertyset, , swbemqualifierset. what selecting ? have tried iterating on collection ? plus need minimum windows vista itemindex method. hope helps
Read more