Solved

Combine two columns

Posted on 2014-12-10
10
69 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
Comment Utility
CONCAT(Fund_C, '', Fund_Shrt_NM)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
I presumed they were trying to add a space or something between them - maybe not...
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
just use "||" instead of "+"

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

Expert Comment

by:John_Vidmar
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:leezac
Comment Utility
Well actually a hyphen ABCD-Trust Dept.  Slightwv worked - no space or hyphen
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
If you want a hyphen:
Fund_C || '-' || Fund_Shrt_NM
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
Comment Utility
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
Comment Utility
My previous browser was not allowing me to award points.  Thanks for help
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now