Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dotsofcolor
Starting Member
12 Posts |
 Posted - 2013-04-02 : 09:37:39
|
| Hello,I have tried this several ways and need some help as I can't seem to get this to work.I have a field called M3_invnum with a Numeric value that can hold up to 8 characters. Then I have a field called M3_suff that is a Char type that holds up to 3 values.Basically I would like it to concatenate like the following with a hyphen in the middle. 1234567-000Any help would be appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-02 : 09:58:13
|
| [code]SELECT CAST(M3_invnum AS varchar(8)) + '-' + M3_Stuff FROM Table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
dotsofcolor
Starting Member
12 Posts |
Posted - 2013-04-02 : 10:24:13
|
| Thanks for the reply. I tried the above but got this.[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ) was not valid. Valid tokens: + - AS <IDENTIFIER>.I should have probably mentioned that the data is coming from DB2 and is going to be used with SQL Server 2008 |
|
|
|
dotsofcolor
Starting Member
12 Posts |
Posted - 2013-04-02 : 11:13:48
|
| Should I be using the pipe || for the concatenation for a DB2 source or will the + still work? I tried it both ways and still got the same error. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-02 : 11:51:03
|
| not sure on DB2. i thought || should wrk...try in some db2 forums.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
dotsofcolor
Starting Member
12 Posts |
Posted - 2013-04-02 : 16:37:46
|
Yup, I did the pipes like so and it was good.( CAST(M3_invnum AS varchar(8)) || '-' ) || M3_suff AS FULLINV Thanks for your help visakh16.  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 01:16:22
|
| ok...cool------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Concatenate a Numeric and Char type with a hyphen