Combine two columns

Posted on 2014-12-10
Medium Priority
Last Modified: 2014-12-19
How do I combine to columns.  I am trying this way but get an error "invalid number".  Both columns are Characters (text and numbers)

SELECT DVSN_NM ,Fund_C + '' + Fund_Shrt_NM) as Fund_Name
Question by:leezac
  • 3
  • 2
  • 2
  • +3
LVL 58

Expert Comment

ID: 40492597
CONCAT(Fund_C, '', Fund_Shrt_NM)
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40492625
Fund_C || Fund_Shrt_NM

An empty string in Oracle is a NULL value so it does nothing to have it.
LVL 58

Expert Comment

ID: 40492629
I presumed they were trying to add a space or something between them - maybe not...
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 61

Expert Comment

ID: 40492657
just use "||" instead of "+"

Fund_C + '' + Fund_Shrt_NM
Fund_C || '' || Fund_Shrt_NM
LVL 11

Expert Comment

ID: 40492658
Incorrect concatenation character and the extra right-parenthesis before as Fund_Name needs to be removed:
,	Fund_C || ' ' || Fund_Shrt_NM as Fund_Name

Open in new window


Author Comment

ID: 40492663
Well actually a hyphen ABCD-Trust Dept.  Slightwv worked - no space or hyphen
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40492666
I would guess that as well but what is posted will not do that.  You would need to physically add the space in it:

Fund_C || ' ' || Fund_Shrt_NM

I missed this when I posted:  Your CONCAT syntax is invalid.  CONCAT takes two parameters only.  Anything more will result in an error.

You use concat with 3 parameters you need to nested concat calls:
CONCAT(Fund_C, CONCAT(' ', Fund_Shrt_NM))
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1800 total points
ID: 40492669
If you want a hyphen:
Fund_C || '-' || Fund_Shrt_NM
LVL 36

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 40497199
In Oracle, the "+" operator is always an arithmetic operator, so it can be used only with numbers, or with a date and a number (to add days or a portion of a day to a date value).  In Oracle you cannot use "+" to combine two character (or string) values.

The simple concatenation operator in Oracle is two adjacent "pipe" characters or vertical bar symbols.  Some other languages may allow the "+" operator to combine string values, but Oracle does not support that.

So, this is legal in Oracle:
SELECT DVSN_NM ,Fund_C || Fund_Shrt_NM) as Fund_Name

And, as slightwv indicated, to get a hyphen between the two values you are combining, you need to use two concatenation operators, like this:
SELECT DVSN_NM ,Fund_C || '-' || Fund_Shrt_NM) as Fund_Name

Note that the spaces in this example *DO NOT* get copied to the output.  They just make the command easier for humans to read.  But, this example is also legal in Oracle, and will give you the same result :
SELECT DVSN_NM ,Fund_C||'-'||Fund_Shrt_NM) as Fund_Name

If you want a space on each side of the hyphen you need to include spaces between the single quotes like this:
SELECT DVSN_NM ,Fund_C||' - '||Fund_Shrt_NM) as Fund_Name

Author Closing Comment

ID: 40509937
My previous browser was not allowing me to award points.  Thanks for help

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question