[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Combine two columns

Posted on 2014-12-10
10
Medium Priority
?
79 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 60

Expert Comment

by:HainKurt
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 1800 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 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
 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 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