Post reply

concat string with numeric

  • November 14, 2012 at 2:34 pm

    #265425

    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

  • November 14, 2012 at 2:36 pm

    #1559133

    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[/url] 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[/url]

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

  • November 14, 2012 at 2:38 pm

    #1559135

    Thanks

    SO this works!!!

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

  • November 14, 2012 at 2:58 pm

    #1559147

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 15, 2012 at 5:28 am

    #1559311

    Thx good advice.. have a great day

    U7

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply