concatenate

  • Can not figure out what I am doing wrong in table on I have FirstName and PhoneNumber and in the second table I have Carrier with @txt.***.Com etc. So when I write a proc to send text I want to be able to concatenate phonenumber and @txt.***.com together

    Example

    Select FirstName

    ,PhoneNumber + SMS

    from Names as N

    inner Join Carrier as C

    on N.CarrierID = C.CarrierID

  • edward_hall76 (4/23/2013)


    Can not figure out what I am doing wrong in table on I have FirstName and PhoneNumber and in the second table I have Carrier with @txt.***.Com etc. So when I write a proc to send text I want to be able to concatenate phonenumber and @txt.***.com together

    Example

    Select FirstName

    ,PhoneNumber + SMS

    from Names as N

    inner Join Carrier as C

    on N.CarrierID = C.CarrierID

    Okay, so what is the problem?

  • What is the datatype of PhoneNumber?

  • I have phonenumber and sms set as Text. The PhoneNumber is in the Names table and SMS is in the Carriers table.

  • edward_hall76 (4/23/2013)


    I have phonenumber and sms set as Text. The PhoneNumber is in the Names table and SMS is in the Carriers table.

    Still haven't answered the question, what is the problem??

  • Can you please confirm that the datatype is Text, as opposed to varchar(???) or nvarchar(???)?

  • I get this error Operand data type text is invalid for add operator

    the PhoneNumber is in the Names table and the sms is in the Carriers table

  • yes they are text

  • edward_hall76 (4/23/2013)


    I get this error Operand data type text is invalid for add operator

    the PhoneNumber is in the Names table and the sms is in the Carriers table

    Okay, now we need to see the DDL (CREATE TABLE statements) for the tables. I'm not sure which column is what data type. Looks like you will need to cast one or both to a varchar data type.

  • Text, ntext and image are special data types that does not support the normal string operators.

    If you want to concatenate, you'll need to cast each field into varchar or nvarchar first.

  • ok thanks changing them to a varchar works now.

  • Mansfield (4/23/2013)


    Text, ntext and image are special data types that does not support the normal string operators.

    If you want to concatenate, you'll need to cast each field into varchar or nvarchar first.

    And they have all been deprecated. Please consider changing your base tables to (n)varchar(max).

    Of course I doubt you really need varchar(max) here because varchar can hold up to 8,000 characters. You can figure out how long your real data is with

    select max(len(cast(YourTextColumn as varchar(max)))) as MaxLen

    _______________________________________________________________

    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/

Viewing 12 posts - 1 through 11 (of 11 total)

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