Solved

Combine two columns

Posted on 2014-12-10
10
74 Views
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
FROM
dbo.V_BAL_MO  BALMO
    --
0
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40492597
CONCAT(Fund_C, '', Fund_Shrt_NM)
0
 
LVL 77

Expert Comment

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

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

Expert Comment

by:Gary
ID: 40492629
I presumed they were trying to add a space or something between them - maybe not...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40492657
just use "||" instead of "+"

Fund_C + '' + Fund_Shrt_NM
>>>
Fund_C || '' || Fund_Shrt_NM
0
 
LVL 11

Expert Comment

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

Open in new window

0
 

Author Comment

by:leezac
ID: 40492663
Well actually a hyphen ABCD-Trust Dept.  Slightwv worked - no space or hyphen
0
 
LVL 77

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))
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 450 total points
ID: 40492669
If you want a hyphen:
Fund_C || '-' || Fund_Shrt_NM
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 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
 FROM
dbo.V_BAL_MO  BALMO

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
0
 

Author Closing Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

737 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