Solved

Combine two columns

Posted on 2014-12-10
10
71 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
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 58

Expert Comment

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

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 51

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 76

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 76

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

777 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