Can You Concatenate Text Datatypes???
Sep 8, 2000I would like to concatenate text datatypes. Is this possible??
I'm using field1 + " " + field2
I cannot convert to varchar since the size is larger than 8000.
Thanks, Vic
ADVERTISEMENT
Text Datatypes
Feb 19, 2001Hi,
Is anybody can tell me why I can't have more than 255 characters in a field with text datatype?
The insert and the retreive is done directly using ISQL...
Thanks a lot for your assistance
Patrick
Datatypes That Preserve Rich Text
Feb 1, 2007Hey there - I have a textbox (C# 1.1 OR 2.0) that I want to take it's contents and post to a datatype of some sort in SQL2k5 and have it preserve the breaks (return keystrokes) so when the data is presented, it can hold a paragraph form. Is this possible?something like this: <asp:TextBox id="txtContent" runat="server" TextMode="MultiLine" Rows="10"></asp:TextBox>Right now, I'm using nvarchar(4000) as the datatype but if I were to type, say 3 paragraphs, the data returned in the datagrid I bind that data to shows only one long string of data. For the time being, I have been puttting HTML formatting tags in the content I'm posting to the DB, but ultimately don't want to have to do that. I don't want to have to use a Text Editor (like FTB or FCKeditor) if necessary because they seem very much involved for the simple need I have which is to preserve paragraph formatting.So, is there another datatype in SQL2k5 that I should be using? Or are there simple code snippets I can utilize that will recognize a carriage return as a </p><p> tag?I'm trying to migrate to 2.0 as much as possible, so any C# code would be greatly appreciated in 2.0!Thanks for your help!iSheahan
View 1 Replies View RelatedImporting Text Files And Converting Datatypes
Oct 8, 2004Hi ya... About to tear my hair out.
I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.
I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple:
CREATE TABLE [Pol_Dump073104]
(
[Product_Type] varchar (12) NULL,
[Benefit] float NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] float NULL,
[Contract_Value] float NULL,
)
I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.
Please help! :mad:
Thanks
Concatenate Two Text Fields
Mar 29, 2006New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
Please advise - thanks!
How Do I Concatenate Text To A Sql Statement
Oct 20, 2005The following sql statement fails:SELECT IdFlight, OrganizerLastName + ", " + OrganizerFirstName + "Flt:" + FlightNumber As Concat FROM TableFlights Order ByOrganizerLastName, OrganizerFirstNameI'm trying to concatenate fields and words, but my asp.net page givesme an error saying " Flt:" is not a field.Thanks,Marvin
View 1 Replies View RelatedConcatenate Text Fields In Query
Oct 11, 2005I have a couple columns in a table that have a data type of Text. In a query I need to concatenate these two columns into one result.
For example
Code:
SELECT (Table1.TextColumn1 + ' ' + Table1.TextColumn2) AS 'Text'
FROM Table1
However when I try this I get the error
Invalid operator for data type. Operator equals add, type equals text.
Concatenate Text And Varchar Fields
May 29, 2008I am trying to add a carriage return to the end of a text field through a script. This is what I'm trying:
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '
' WHERE Column_TEXT = 'Some text'
I also tried
UPDATE Table_Name SET Column_TEXT = Column_TEXT) + '<cr>' WHERE Column_TEXT = 'Some text'
But I keep getting the error
The data types text and varchar are incompatible in the equal to operator.
Help!!
Thanks in advance
Mangala
Problem With Updatetext In SQL 2000 Trying To Concatenate Multiple Text Fields
Jan 2, 2007Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan
View 4 Replies View RelatedConcatenate Strings After Assigning Text In Place Of Bit Strings
Feb 19, 2007I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
DataTypes
Sep 13, 2000I am designing my first SQL database and need some insight on datatypes.
What datatype is equivalent to autonumber like in MS Access.
When I convert an auto number field to to SQL it comes as int with a length of 4, Does this mean it will only auto increment to 9999?
I need the field to increment forever.
Also what datatypes should be used for phone number with area code and
zip code.
Thanks
Datatypes
Nov 20, 2000Hi i'm using the FileSystemObject to loop through a file and read out all the data within it (it's an html page, so loads of tags and free text) and then put it in a field in a table. I am using the nText datatype but i don't think i use Full-Text Searching with this, is there any other i can use that produces the same result?
Cheers in advance...
Datatypes
Apr 10, 2002Hello,
I'm 17 years old and I'm from Belgium, and I have to make a school project. I have to make a program in Visual Basic and my database is SQL Server 2000. I've already made my tables but I dont know excactly what all the datatypes in SQL Server are. They are a little bit different then Access datatypes and I dont know what to use, ntext, text, nvar, ... I can't find a list of the meaning of the datatypes on the net, maybe I'm searching with the wrong keywords(+"sql server" +datatypes in yahoo), but could please someone give me a link of a site where there is that kind of information?
thanks a lot :)
Dommie x
Datatypes
Aug 4, 2004I'm new to SQLServer and I'm creating some tables. In Oracle I always used varchar as this was the default and also recommended. The default in SQLServer seems to be char. Am I right in thinking that varchar is still best to use, as the field can then be of variable length rather than fixed?
View 4 Replies View RelatedDatatypes
Aug 27, 2005Could someone tell me the datatypes for the SQL EXPRESS 2005
I'm trying to use a boolean datatype and it says it isn't a datatype....have they changed it??? Please HELP!!!
Thanks
SQL Datatypes
Apr 1, 2007I have a SQL 2005 database that's created by a survey data collectionsystem. Users of this system are fairly non-technical and have littleto no conscious control over the datatypes. As a result, the data ismostly stored quite inefficiently as varchars. For example, there isdata that could be stored in a column of bits and it's stored as avarchar value of 0 or 1. (Yuck, I know.)I am building a reporting system using this raw data and have a newtable structure designed that is much more efficient (and better forreporting). Does anyone have any suggestions for getting this datainto my new structure? Specifically, how would you recommend checkingthat varchar field and determining it could be stored as a bit?
View 3 Replies View RelatedSql Datatypes
Sep 29, 2006i hope this is the right board for this.
i want to have a table for members of a club/society and i wnt to be able to maintain the structure of their member id numbers, eg com212 for a committee member, or mem019 for ordinary members.
is there a way to automate this using some sort of auto-incriment function and use this as the primary key for the table?
thanks in advance,
mark
SQL Datatypes
Apr 17, 2007I have created a coldfusion webpage form that writes to a sql database. For comment boxes they can enter as much text as they need. Do I want to choose a varchar for my datatype and put a riduculously high number for characters or should I should use a text datatype and if I use text are there specific options I should set up?
Thanks!!!
SQL Server Datatypes
Nov 18, 2006I am currently creating a database tables in SQL Server 2005. I am looking for a link to a website that might explain to me the correct configuration for tabels in a sql server database such as the recommended datatypes for specific fields, eg datatype for short characters, or numbers etc.
View 3 Replies View RelatedSQL Server Datatypes
Jun 3, 2007Can some1 tell me the data types used in SQL Server for what like, i use Access so like i feel the data types are more intuitive, but for SQL Server in ASP, nchar, nvarchar, there are so many and i dunno what is what. any sites for beginners? cos i searched google and the terms they use to describe data types are like "variable length" "Unicode" i don't exactly know them. something like A-B, integers etc will be good.
View 2 Replies View RelatedConverting Datatypes
Jun 9, 2000Two datatype problems: (1) Datetime conversion (2) Foreign Currency format.
The first problem is that I am trying to convert source data from varchar to datetime. The source data is in CSV format and is displayed as follows - '1111999052349' to represent 1-Nov-1999 05:23:49. Have converted to a numeric value and then tried to convert to datetime but this just returns the following message 'Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.' However you can convert to a timestamp but the value returned is not meaningful (e.g. 0x0E0000013DFA4EE8).
Problem 2 concerns the French currency. The source data is in CSV format and is displayed as follows - '00000001,9700' to represent Fr1.97. Need to convert this to a numeric field or alternatively get SQL 7 to recognise it as a money field.
Any suggestions would be musch appreciated.
Many Thanks.
Exporting BIT Datatypes?
Jun 24, 2004Hi all. Im tryin to export (DTS) my some SQL server tables, many of which contain 'bit' datatypes. However, when DTS/SQL Serv. moves these bit datatypes out, it changes bit values to True/False values - which makes sense - however these are all going to plugin to web frontends where the SQL specifies condtions like: "if column1 = 0 then" etc..
Is there anyway to get SQL server to export bit datatypes as just numeric values of 0/1?
Converting Datatypes, Not Sure If This Is Right?
Sep 28, 2007New to developing. We are working on a project using asp.net and c# where data is pulled out of an sql database via multiple tables and displayed via a details view. The user can then update the information and submit it back to the database.
The problem we are encountering is:
Within the database there are multiple tables joined by a FK for example there is a Service Group ID column with the Records main table linked to the Service Group table which holds an ID column and a Service Group column so ID1 = Service Group A. Any records within the main table which has an ID of 1, is shows as Service Group A in the details view.
What we wold like to do is when a user updates the Service Group via a drop down, the ID goes into the main table rather than the Service Group name so if they select Service Group B, an ID of 2 goes into the Records main table. We aren't sure how to go about doing the conversion of Service Group B to an ID 2? We have been looking at converting datatypes but don't think it is right for what we are trying to do,
Any suggestions would be gratefully recieved Apologies if this is in the wrong forum.
thanks
I Need A Tutorial On How To Use DataTypes In T-SQL
Oct 10, 2007Hi all,
i'm looking for an article on how to use DataTypes of SQLServer in T-Sql script writing !!!?
As you know the Books-Online has limited example and i have trouble undertanding for example how to use Binary,varbinary Decimal,Float,real...in T-sql script
Could anyone showm me an instructive article on the net?
Thanks in advance.
Geometric Datatypes
May 23, 2006Hi,
Is there any equivalent in SQL Server for Oracle's datatype 'SDO_GEOMETRY'
Smitha
Problem With Datatypes...
Apr 27, 2007Dear Friends,
I have a problem with datatypes....
The error is "Cannot convert between unicode and non-unicode..."
The column in source is nvarchar(32) and in the destination is varchar(32)... ok...
But in the DataReader Source in the SQLCommand I've converted the column into varchar(32) with
convert(varchar(32), Equities.Equities_Name) as CL_FOLDER_ID
Do you have any tip??Thanks!
Detecting LOB Datatypes.
Oct 15, 2007
Does anyone have a good way to detect LOB datatypes for a table or index? I am doing index rebuilds with the ONLINE option and need to work around indexes with LOB datatypes in them...
Any suggestion?
Converting Datatypes:
Sep 25, 2006Hi All,
how do you convert from a date to an int ? as well as converting from Varchar to and Int
in SQL server 2000 ?
I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e
DECLARE @DateValue AS VARCHAR(20)
SELECT @DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'
print convert(int,@DateValue) - 5
print GetDate() - 35
SELECT ccy_code, NULL, xrate_date, sterling_xrate
FROM SylvanTrans.dbo.SIADHP_XRate_Hist
WHERE xrate_date >= (CONVERT(int, @DateValue) - 35 )
ORDER BY xrate_date
now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @DateValue Variable, but the conversion throws a syntax error:
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'GetDate()' to a column of data type int.
what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!
thanks
regards
How Can I Combine Three Fields Together Of Dif Datatypes
Oct 13, 2007I want to combine three fields together as a description in a select statement. When I try using the & or + I'm told that the datatypes are incompatable. How can I join them?
(Item_Description is Nvarchar, Item_Cost is Money, Is_Active is bit)
Select Item_Description &' '& Item_Cost &' '& Is_Active As Description FROM tblItemList I tried Casting this but same incompatable message.
Select Item_Description &' '& CAST(Item_Cost AS NVARCHAR) &' '& CAST(Is_Active AS NVARCHAR) As Description FROM tblItemList
What I'm hoping to end up with is:
Brake Pedal, $36.00, True
SQL Server 2000 Datatypes!!!
Jan 14, 2005Hello to ALL the ASP.Net, ASP, and SQL Gods!!!!
I have a quick question about SQL Server 2000 supported datatypes!!
I have been trying to search on the "dreaded" Microsoft KB site to try and find information and details about the different supported data types on SQL Serv 2000, and to no avail!!!
Could anyone help me out and give me a rundown on the different data types that are supported on SQL Serv 2000, and maybe a quick explanation for each datatype, so I can make a better database?!??! I would really much appreciate any advice that anyone could offer...
Thanx in advance!!!
CAOTK
Difference Between DataTypes In SQL 2005?
Apr 11, 2006What is the difference between binary and image dataType.
When and how should I use them?
User Defined Datatypes
Jan 5, 1999Is it possible to change the owner of a User Defined Dataype? I've
recently had to change the owner of one of my databases and the
tables from a specific user to 'dbo'. The UDD's are the only objects
I am unable to change the ownership of.
Any suggestions anyone?
Thanks for any advice!
Toni Eibner
Bit Datatypes Physical Storage
Feb 15, 2005Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).
Is there any good documentation about the physical storage layout for a data page?
Thanks -