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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2004-11-08 : 07:52:54
|
i'm trying to contcatenate 2 ntext fields to one.Here is my sqlSELECT CountryCode,FirstName,LastName,Gender,DayPhoneNumber,DayPhoneExt,EvePhoneNumber,Cellnumber,besttimetocall+notes as snotes from users WHERE vm=1 and todate >= CONVERT(datetime, '11/8/2004', 101) AND todate <= CONVERT(datetime, '11/9/2004', 101) and x=7 The 2 fields I am concatenating are besttimetocall and notes.What am I doing wrong?my error is Server: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals ntext. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 07:55:38
|
| You cannot concatenate text, ntext or image datatypes. The best you can do is convert the ntext to nvarchar(4000) and concatenate it.Be advised if besttimetocall is a datetime you cannot concatenate that either. That would also need to be converted to varchar or nvarchar first.Why do you need to concatenate them anyway? |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-11-08 : 07:59:51
|
| besttimetocall is a varchar and notes is a ntext.How do I convert in the sql to a varchar?one admin wants to see the besttimetocall as part of the notes and the rest want it separate |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 08:02:27
|
| SELECT CountryCode, FirstName, LastName, Gender, DayPhoneNumber, DayPhoneExt, EvePhoneNumber, Cellnumber, cast(besttimetocall as nvarchar(20) + cast(notes as nvarchar(4000)) as snotes from users WHERE vm=1 and todate BETWEEN '11/8/2004' AND '11/9/2004' and x=7Note that you will lose any data after the 4,000th character in the notes column. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-11-08 : 08:27:00
|
| seems to work but snotes is always null. Notes is not null |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 08:30:39
|
| If besttimetocall is null then you'll get a null for the concatenation. Use this instead:SELECT CountryCode, FirstName, LastName, Gender, DayPhoneNumber, DayPhoneExt, EvePhoneNumber, Cellnumber, cast((IsNull(besttimetocall,'') as nvarchar(20)) + cast(IsNull(notes,'') as nvarchar(4000)) as snotes from users WHERE vm=1 and todate BETWEEN '11/8/2004' AND '11/9/2004' and x=7There was a mistake in my earlier code too, try this instead and see if it works. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-11-08 : 08:33:26
|
| Incorrect syntax near the keyword 'as'. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-08 : 08:39:29
|
| SELECT CountryCode, FirstName, LastName, Gender, DayPhoneNumber, DayPhoneExt, EvePhoneNumber, Cellnumber, cast(IsNull(besttimetocall,'') as nvarchar(20)) + cast(IsNull(notes,'') as nvarchar(4000)) as snotes from users WHERE vm=1 and todate BETWEEN '11/8/2004' AND '11/9/2004' and x=7 |
|
|
|
|
|
|
|
|
concatenate string help