Combining Name Fields

[gfisch13]

Hi Everyone,  I have First Name and Last Name in two separate fields and would like to combine them into FullName.

I have just tried this action and am receiving an error message, the name fields are the same data type, unsure why this happened???

Any help is appreciated.   Is is the load statement from my script and I was trying to do the combination on the fly.

SQL##f - SqlState: 37000, ErrorCode: 402, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The data types nvarchar and varchar are incompatible in the '&' operator.

SQL SELECT "ACCT_NUM",

    "ACCT_STTS_CTGY_CD",

    "AUDT_ID",

    "CMDTY_CD",

    "DATA_SRC_CD",

    "EMPE_FRST_NM",

    "EMPE_ID",

    "EMPE_LST_NM",

  "EMPE_FRST_NM" & ' ' & "EMPE_LST_NM" as "CitiFullName",

    "ETL_CYC_DT",

    "HRCH_LVL_1_CD",

    "HRCH_LVL_1_NM",

    "HRCH_LVL_2_CD",

    "HRCH_LVL_2_NM",

    "HRCH_LVL_3_CD",

    "HRCH_LVL_3_NM",

    "ITEM_DSCR",

    "LN_ITEM_DSCR",

    MCC,

    "MCC_CTGY",

    "MCC_DSCR",

    "PRCH_HRCH_NM",

    "TCKT_NUM",

    "TRAN_AMT",

    "TRAN_CD_GRP",

    "TRAN_CUR_CD",

    "TRAN_DB_CR_IND",

    "TRAN_DT",

    "TRAN_TYP_DSCR",

    "VND_CITY_NM",

    "VND_CNTRY_CD",

    "VND_NM",

    "VND_ST_CD",

    "VND_ZIP_CD"

FROM "DMBA_SIU".dbo."T_CITICARD_CHRG_PRST"

[zebhashmi · Accepted solution]

you are using different syntax  

so try this

"EMPE_FRST_NM", "EMPE_LST_NM" as "CitiFullName",

or

"EMPE_FRST_NM"+"EMPE_LST_NM" as "CitiFullName",

[Anil_Babu_Samineni]

May be do this?

Load *,

  "EMPE_FRST_NM" & ' ' & "EMPE_LST_NM" as "CitiFullName";

SQL SELECT "ACCT_NUM",

    "ACCT_STTS_CTGY_CD",

    "AUDT_ID",

    "CMDTY_CD",

    "DATA_SRC_CD",

    "EMPE_FRST_NM",

    "EMPE_ID",

    "EMPE_LST_NM",

    "ETL_CYC_DT",

    "HRCH_LVL_1_CD",

    "HRCH_LVL_1_NM",

    "HRCH_LVL_2_CD",

    "HRCH_LVL_2_NM",

    "HRCH_LVL_3_CD",

    "HRCH_LVL_3_NM",

    "ITEM_DSCR",

    "LN_ITEM_DSCR",

    MCC,

    "MCC_CTGY",

    "MCC_DSCR",

    "PRCH_HRCH_NM",

    "TCKT_NUM",

    "TRAN_AMT",

    "TRAN_CD_GRP",

    "TRAN_CUR_CD",

    "TRAN_DB_CR_IND",

    "TRAN_DT",

    "TRAN_TYP_DSCR",

    "VND_CITY_NM",

    "VND_CNTRY_CD",

    "VND_NM",

    "VND_ST_CD",

    "VND_ZIP_CD"

FROM "DMBA_SIU".dbo."T_CITICARD_CHRG_PRST";

[sunny_talwar]

Have you tried it like this? (https://www.techonthenet.com/sql_server/functions/concat.php)

CONCAT( string1, string2, ... string_n)

CONCAT("EMPE_FRST_NM", ' ', "EMPE_LST_NM") as "CitiFullName",

Or you can use + to concatenate them (+ (String Concatenation) (Transact-SQL) | Microsoft Docs)

"EMPE_FRST_NM" + ' ' + "EMPE_LST_NM" as "CitiFullName",

[gfisch13]

Thank you for the response.  I will look into using this function as there is utility beyond what I did here.

[gfisch13]

Anil - I had tried this previously and I got an error message I think because those fields hadn't loaded yet.  Thank you for your response!!!!