SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Concatenate a Numeric and Char type with a hyphen
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dotsofcolor
Starting Member

12 Posts

Posted - 04/02/2013 :  09:37:39  Show Profile  Reply with Quote
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-000

Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/02/2013 :  09:58:13  Show Profile  Reply with Quote

SELECT CAST(M3_invnum AS varchar(8))  + '-' + M3_Stuff FROM Table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dotsofcolor
Starting Member

12 Posts

Posted - 04/02/2013 :  10:24:13  Show Profile  Reply with Quote
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
Go to Top of Page

dotsofcolor
Starting Member

12 Posts

Posted - 04/02/2013 :  11:13:48  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/02/2013 :  11:51:03  Show Profile  Reply with Quote
not sure on DB2. i thought || should wrk...try in some db2 forums.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dotsofcolor
Starting Member

12 Posts

Posted - 04/02/2013 :  16:37:46  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/03/2013 :  01:16:22  Show Profile  Reply with Quote
ok...cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000