concat string with numeric

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » concat string with numeric

concat string with numeric

Rate Topic

Display Mode

Topic Options

Author

Message

jbalbo

Posted Wednesday, November 14, 2012 2:34 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:00 PM
Points: 152, Visits: 222

I know this is pretty basic ...

but how do I concat string and numeric?

I wanted Client.LName + ', ' + Client.FName + ' -' & client.ID as ClientName

where client.id is numeric

Thanks
in advance

Joe

Post #1384870

CapnHector

Posted Wednesday, November 14, 2012 2:36 PM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:07 AM
Points: 935, Visits: 1,709

You need to convert the numeric value to a text value. http://msdn.microsoft.com/en-us/library/ms187928.aspx Once all the data is strings you can concat to your hearts content.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1384872

jbalbo

Posted Wednesday, November 14, 2012 2:38 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:00 PM
Points: 152, Visits: 222

Thanks

SO this works!!!

Client.LName + ', ' + Client.FName + ' -' + cast(client.ID as nvarchar) as ClientName

Post #1384874

Sean Lange

Posted Wednesday, November 14, 2012 2:58 PM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204

jbalbo (11/14/2012)

Thanks

SO this works!!!

Client.LName + ', ' + Client.FName + ' -' + cast(client.ID as nvarchar) as ClientName

Be very careful about using the default lengths for datatypes. When you don't specify the length you get the default size, and this can vary depending on context. I would add a length for your nvarchar. I don't know what datatype your ID is but 10 should be plenty of room. Make it smaller if you don't need that much length, or longer if needed.

Client.LName + ', ' + Client.FName + ' -' + cast(client.ID as nvarchar(10))  as ClientName

/soapbox on

As a side note, it is often considered poor naming convention to have ID. What happens when you have two tables with a PK named ID? Something like ClientID is probably a better choice. One general rule for databases is that is usually preferred that a column name not change it's name when referenced as a foreign key. For example. If you have an Order table and the PK is ID it gets awfully confusing. You have a column with the same name in two tables but you join those tables on Order.ClientID = Client.ID where Order.ID = 8473. That just makes my head spin. If those same two table were aliased O and C the join becomes very clear (O.ClientID = C.ClientID where O.OrderID = 8473

/soapbox off

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1384889

jbalbo

Posted Thursday, November 15, 2012 5:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:00 PM
Points: 152, Visits: 222

Thx good advice.. have a great day

U7

Post #1385101

« Prev Topic | Next Topic »

Permissions