How To Update Text Field
Hi all,
I have a table with millions of records, table has three fields: case_id,line_no and notedata field, notedata field is 60 chars long, datatype varchar.for each case_id there could be as many as 2000 line_no meaning 2000 notes. I need to compress these notes into one note by case_no, For example case_no 1 could have 2000 lines of notes but my comressed table shoul have only one line containing all 2000 notes in line_no sequence.
my compressed table contains two fields case_no and notetext, notetext is a text field.
here is the script I am trying to use to accomplish the task but it does not append more than 8000 chars in one case, so my notes are chopping of, how should I do this, please let me know of any suggestions..
Thanks.
truncate table eldoecinotescompressed
insert into eldoecinotescompressed (app_code, case_no)
select distinct app_code, substring(system_key, 6,8)
from eldoecinotes
DECLARE @case VARCHAR(20);
DECLARE @note VARCHAR(80);
DECLARE @lineno VARCHAR(5);
DECLARE notes_cursor CURSOR FOR
select substring(system_key, 6,8) case_no, line_no, rtrim(notedata) notedata FROM EldoECINotes
where substring(system_key, 6,8)<>''
order by 1,2;
OPEN notes_cursor;
FETCH NEXT FROM notes_cursor into @case, @lineno, @note;
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRANSACTION;
update eldoecinotescompressed
set notetext =
(case when isnull(datalength(notetext), 0) >= 0 then
substring(isnull(notetext,''), 1, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 8000 then
substring(isnull(notetext,''), 8001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 16000 then
substring(isnull(notetext,''), 16001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 24000 then
substring(isnull(notetext,''), 24001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 32000 then
substring(isnull(notetext,''), 32001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 40000 then
substring(isnull(notetext,''), 40001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 48000 then
substring(isnull(notetext,''), 48001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 56000 then
substring(isnull(notetext,''), 56001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 64000 then
substring(isnull(notetext,''), 64001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 72000 then
substring(isnull(notetext,''), 72001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 80000 then
substring(isnull(notetext,''), 80001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 88000 then
substring(isnull(notetext,''), 88001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 96000 then
substring(isnull(notetext,''), 96001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 104000 then
substring(isnull(notetext,''), 104001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 112000 then
substring(isnull(notetext,''), 112001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 120000 then
substring(isnull(notetext,''), 120001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 128000 then
substring(isnull(notetext,''), 128001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 136000 then
substring(isnull(notetext,''), 136001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 144000 then
substring(isnull(notetext,''), 144001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 152000 then
substring(isnull(notetext,''), 152001, 8000)
else '' end ) +
(case when isnull(datalength(notetext), 0) > 0 then
char(13) + char(10)
else '' end) +
isnull(@note,'')
where case_no=@case;
commit;
FETCH NEXT FROM notes_cursor into @case, @lineno, @note;
END
CLOSE notes_cursor;
DEALLOCATE notes_cursor;
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Update A Text Field In T-SQL 2000.. How ??
First i use openxml to get my data to update the other server with webservices. my prob is that i cant update text Fields because i got an error ================ OLE DB provider 'OpenXML' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'OpenXML' IRowset::RestartPosition returned 0x80004005: The provider did not give any information about the error.]. ================ what would be my best shot here... thanx ======================================= XEC sp_xml_preparedocument @handle OUTPUT, @data begin transaction SELECT * FROM TblEvenement WHERE idEvenement = 95 UPDATE TblEvenement SET TblEvenement.idEvenement = isnull(iox.idEvenement,TblEvenement.idEvenement), TblEvenement.sNomEvenement = isnull(iox.sNomEvenement,TblEvenement.sNomEvenement), TblEvenement.sDescriptionCourte = isnull(iox.sDescriptionCourte,TblEvenement.sDescriptionCourte), TblEvenement.sDescriptionLongue = isnull(iox.sDescriptionLongue,TblEvenement.sDescriptionLongue), TblEvenement.IdTypeSalle = isnull(iox.IdTypeSalle,TblEvenement.IdTypeSalle), /*TblEvenement.imgSalle = isnull(iox.imgSalle,TblEvenement.imgSalle),*/ TblEvenement.sNomArtiste = isnull(iox.sNomArtiste,TblEvenement.sNomArtiste), TblEvenement.bAfficherInternet = isnull(iox.bAfficherInternet,TblEvenement.bAfficherInternet), TblEvenement.nNbBilletLimite = isnull(iox.nNbBilletLimite,TblEvenement.nNbBilletLimite), TblEvenement.bLivraisonCourrier = isnull(iox.bLivraisonCourrier,TblEvenement.bLivraisonCourrier), TblEvenement.IdTypeRepresentation = isnull(iox.IdTypeRepresentation,TblEvenement.IdTypeRepresentation), TblEvenement.sDetailInternet = isnull(iox.sDetailInternet,TblEvenement.sDetailInternet), TblEvenement.bHistorique = isnull(iox.bHistorique,TblEvenement.bHistorique), TblEvenement.bAdmissionGenerale = isnull(iox.bAdmissionGenerale,TblEvenement.bAdmissionGenerale), TblEvenement.bEvenementEnVente = isnull(iox.bEvenementEnVente,TblEvenement.bEvenementEnVente), TblEvenement.IdProducteur = isnull(iox.IdProducteur,TblEvenement.IdProducteur), TblEvenement.bEvenementDemo = isnull(iox.bEvenementDemo,TblEvenement.bEvenementDemo), /*TblEvenement.sLogoBOCA = isnull(iox.sLogoBOCA,TblEvenement.sLogoBOCA),*/ /*TblEvenement.sLogoLP2722 = isnull(iox.sLogoLP2722,TblEvenement.sLogoLP2722),*/ TblEvenement.sDescriptionCourte_En = isnull(iox.sDescriptionCourte_En,TblEvenement.sDescriptionCourte_En), /*TblEvenement.sDescriptionLongue_En = isnull(iox.sDescriptionLongue_En,TblEvenement.sDescriptionLongue_En),*/ TblEvenement.sDetailInternet_En = isnull(iox.sDetailInternet_En,TblEvenement.sDetailInternet_En) FROM OPENXML (@handle, N'//TblEvenement') WITH ( idEvenement int, sNomEvenement nvarchar (100), sDescriptionCourte nvarchar (100), sDescriptionLongue text, IdTypeSalle int, imgSalle image, sNomArtiste nvarchar (100), bAfficherInternet bit, nNbBilletLimite int, bLivraisonCourrier bit, IdTypeRepresentation int, sDetailInternet nvarchar (100), bHistorique bit, bAdmissionGenerale bit, bEvenementEnVente bit, IdProducteur int, bEvenementDemo bit, sLogoBOCA text, sLogoLP2722 text, sDescriptionCourte_En nvarchar (100), sDescriptionLongue_En text, sDetailInternet_En nvarchar (100), sNomEvenement_En nvarchar (100) ) iox WHERE TblEvenement.idEvenement = iox.IdEvenement /*and WRITETEXT (TblEvenement.sDescriptionLongue @ptrval 'Salut')*/ SELECT * FROM TblEvenement WHERE idEvenement = 95 rollback EXEC sp_xml_removedocument @handle
View Replies !
View Related
Appending To A Text Field Using UPDATE
I would like to update a field that already has data in it and I dont' want to overwrite the existing text. Here is my existing statement UPDATE wr SET cf_notes = " + tmp_array(24) + " WHERE wr_id = " + data_temp(0) I would like to add cf_notes + tmp_array(24) to cf_notes. Is this possible in SQL? If so, what is the correct syntax. I have tried 6 different statements and I get a compile error on every statement. Thanks, SBR
View Replies !
View Related
Openxml - Update Text Field In T-sql
I got an error when i try to udate any of my text field. Is there a good way to do it EXEC sp_xml_preparedocument @handle OUTPUT, @data begin transaction SELECT * FROM TblEvenement WHERE idEvenement = 95 UPDATE TblEvenement SET TblEvenement.idEvenement = isnull(iox.idEvenement,TblEvenement.idEvenement), TblEvenement.sNomEvenement = isnull(iox.sNomEvenement,TblEvenement.sNomEvenemen t), TblEvenement.sDescriptionCourte = isnull(iox.sDescriptionCourte,TblEvenement.sDescri ptionCourte), TblEvenement.sDescriptionLongue = isnull(iox.sDescriptionLongue,TblEvenement.sDescri ptionLongue), TblEvenement.IdTypeSalle = isnull(iox.IdTypeSalle,TblEvenement.IdTypeSalle), /*TblEvenement.imgSalle = isnull(iox.imgSalle,TblEvenement.imgSalle),*/ TblEvenement.sNomArtiste = isnull(iox.sNomArtiste,TblEvenement.sNomArtiste), TblEvenement.bAfficherInternet = isnull(iox.bAfficherInternet,TblEvenement.bAffiche rInternet), TblEvenement.nNbBilletLimite = isnull(iox.nNbBilletLimite,TblEvenement.nNbBilletL imite), TblEvenement.bLivraisonCourrier = isnull(iox.bLivraisonCourrier,TblEvenement.bLivrai sonCourrier), TblEvenement.IdTypeRepresentation = isnull(iox.IdTypeRepresentation,TblEvenement.IdTyp eRepresentation), TblEvenement.sDetailInternet = isnull(iox.sDetailInternet,TblEvenement.sDetailInt ernet), TblEvenement.bHistorique = isnull(iox.bHistorique,TblEvenement.bHistorique), TblEvenement.bAdmissionGenerale = isnull(iox.bAdmissionGenerale,TblEvenement.bAdmiss ionGenerale), TblEvenement.bEvenementEnVente = isnull(iox.bEvenementEnVente,TblEvenement.bEveneme ntEnVente), TblEvenement.IdProducteur = isnull(iox.IdProducteur,TblEvenement.IdProducteur) , TblEvenement.bEvenementDemo = isnull(iox.bEvenementDemo,TblEvenement.bEvenementD emo), TblEvenement.sLogoBOCA = isnull(iox.sLogoBOCA,TblEvenement.sLogoBOCA), TblEvenement.sLogoLP2722 = isnull(iox.sLogoLP2722,TblEvenement.sLogoLP2722), TblEvenement.sDescriptionCourte_En = isnull(iox.sDescriptionCourte_En,TblEvenement.sDes criptionCourte_En), TblEvenement.sDescriptionLongue_En = isnull(iox.sDescriptionLongue_En,TblEvenement.sDes criptionLongue_En), TblEvenement.sDetailInternet_En = isnull(iox.sDetailInternet_En,TblEvenement.sDetail Internet_En) FROM OPENXML (@handle, N'//TblEvenement') WITH ( idEvenement int, sNomEvenement nvarchar (100), sDescriptionCourte nvarchar (100), sDescriptionLongue text, IdTypeSalle int, imgSalle image, sNomArtiste nvarchar (100), bAfficherInternet bit, nNbBilletLimite int, bLivraisonCourrier bit, IdTypeRepresentation int, sDetailInternet nvarchar (100), bHistorique bit, bAdmissionGenerale bit, bEvenementEnVente bit, IdProducteur int, bEvenementDemo bit, sLogoBOCA text, sLogoLP2722 text, sDescriptionCourte_En nvarchar (100), sDescriptionLongue_En text, sDetailInternet_En nvarchar (100), sNomEvenement_En nvarchar (100) ) iox WHERE TblEvenement.idEvenement = iox.IdEvenement SELECT * FROM TblEvenement WHERE idEvenement = 95 rollback EXEC sp_xml_removedocument @handle
View Replies !
View Related
Appending Data To A Text Field In Update Query?
I would like to run an update query that will update multiple rows and append a value to a text field. In theory i would want something that would function like this if possible: update table1 set field1 = field1 + 'some text to be appended' where field2 = value field1 is of datatype Text Does anyone know of a way to acomplish this? Thanks in advance!
View Replies !
View Related
Can I Insert/Update Large Text Field To Database Without Bulk Insert?
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View Replies !
View Related
Problem With Text Field: Text Input Too Long, Weird Characters
Hi, Im a programmer for an university webportal which uses php and msssql. When an user creates a new entry and his text is too long the entry is cut short and weird characters appear at the end of the entry. For example: http://www.ttz.uni-magdeburg.de/scripts/test-messedb/php/index.php?option=show_presse&funktion=presse_show_mitteilung&id=333 How can I set the text limit to unlimited? Could it be something else? Is there a way of splitting an entry to several text fields automatically? Thanks in advance for any help you can give me, Chris
View Replies !
View Related
Create Date Field From Substring Of Text Field
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View Replies !
View Related
Access Memo Field To SQL Server Text Field
Hi, I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all). I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data. Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
View Replies !
View Related
MS Access Memo Field To SQL Server Text Field
Hi all, i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database. I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example. Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason. I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats. Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds! cheers Dominic
View Replies !
View Related
How Can I Parse Text Held In MS SQL 2005 Text Field
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it. I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns) For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal. <ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
View Replies !
View Related
Compare Date Field To Text Field
Hi, I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want. The beginning of the SQL query looks like this: FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦ It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM. I hope someone out there can help me with making this comparison. I appreciate your help. Bill
View Replies !
View Related
Adding Text To A TEXT Field (MS-SQL2000)
Hi everyone, I'm extremely new to SQL so be nice I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth). The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below. Code: Update AR_Primary_asset Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed' Where AR_Primary_Asset.ASSET_REF in ('1','2','4') I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) . Code: DECLARE @Dispose binary(16) SELECT @Dispose = TEXTPTR(DESCRIPTION) FROM AR_PRIMARY_ASSET WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3') UPDATETEXT AR_PRIMARY_ASSET.DESCRIPTION @ptrval null null ' -- Disposed' So i wrapped it into a cursor, this worked on my test SQL server which runs SQL2005. Code: DECLARE @Dispose varbinary(16) DECLARE cursor1 CURSOR FOR SELECT TEXTPTR(DESCRIPTION) FROM AR_Primary_Asset Where AR_Primary_Asset.ASSET_REF in('1','2','3') OPEN cursor1 FETCH NEXT FROM cursor1 INTO @Dispose WHILE @@FETCH_STATUS = 0 BEGIN UPDATETEXT AR_Primary_Asset.DESCRIPTION @Dispose NULL NULL ' -- Disposed' FETCH NEXT FROM cursor1 INTO @Dispose END CLOSE cursor1 DEALLOCATE cursor1 But when it was run on our SQL2000 server it gave the following error message Quote: Msg 403, Level 16, State 1, Line 1 Invalid operator for data type. Operator equals add, type equals text. I've never used vars, cursors, updatetext or even text fields before. So maybe I am going about it totally the wrong way. Is anyone able to tell me a better way to write this? or how to make it compatible to SQL2000?
View Replies !
View Related
Update SQL Field With Stripped Data From Other Field
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View Replies !
View Related
Fail To Update Field With A Field Uniqueidentifier
Hi all, I have a problem about a query to update a table UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288} where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
View Replies !
View Related
Should I Use Text Field Or Binary Field ?
Application is ocr'ing tiff image files and then storing the resultant text data in a text field in SQL 2005 database. This field is then used with the full text catalog. All works fine, However, am I using the correct field type to store the text files for efficiency and space saving? If I use a binary field, does this reduce the size of the database by compressing the text data in the binary field? Also, is there a limit as to the size of text file that I could store in a binary field? It would be good to get feedback on this before I go too far down the wrong road. So, text field to store the text data or binary field to store the actual text file?
View Replies !
View Related
How Do I Update A Field Only If Update Value Is Not Null?
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that? What I mean is something like: --------------------------------------------------- Update database.dbo.table set if(@newvalue !=null) afield = @newvalue; ------------------------------------------------- Really need your help on this. Thanks.
View Replies !
View Related
Long Text Field
Hi I have a textbox field that take 2000 characters from user..Then I used a store procedure to save that user input into database through an insert statement, but for some reason it just never store the whole string of 2000 characters but only store some of it (like 100 or something) .. Seems like a data type problem…(I am using SQL server 2000) This is what I have defined: --------------------------------------- In storeprocedure: @iidea2 varchar(2000)
View Replies !
View Related
Searching Text In A Db Field
Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...declare @s varchar(20)set @s='4,8'EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')
View Replies !
View Related
Problem With Text Field
Hi, I am haveing a field called as description in a table called info in the database. Now when I give the following query: select * from info where description = 'test' it gives me error: Microsoft OLE DB Provider for SQL Server (0x80040E14) The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. How can I do the above. Thanks in advance, Uday.
View Replies !
View Related
SQL Server Text Field
Is there a length limit on retrieving Microsoft SQL Server text fields?ThanksSiobhan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
View Related
Text Field Compare
I am a bit of a SQL Server newbie and have a question. I'm trying to compare two text fields. Both are 56 character fields and are a company name (one company want to see how much customer overlap they have with a newly acquired company). As you can imagine the names are a bit different in each ABN AMRO versus ABN AMRO INC. I tried comparing the first 6, 7, 8 characters with some success. Is there a more advanced way to do this? I appreciate the help.
View Replies !
View Related
Text Field Copying
I am working with a homecare application that stores nursing notes into a text field in the database. The database is growing exponentially and I want to create a procedure that select all notes older than six months, write it to an OS file, then deletes the record in the database. I am doing the writing using a cursor, however this is very slow. Has anyone done anything similar? Let me know.. Thanks
View Replies !
View Related
Substring On Text Field
Hi, According to the docs, when using substring on a text field (not varchar): substring (<text>, start, length) ...the length in this case, represents BYTEs as opposed to number of characters. So my question is, how many bytes per character (or characters per byte)....or is this a possible conversion? thanks,
View Replies !
View Related
Replication - TEXT Field
I have a table that has a TEXT field and I want to replicate it. It already has a primary key field in it. I read that in SQL Server 6.5, you can do transactional replication on a table that has a TEXT field in it. I'm wondering if I need to do any other special configuration to replicate this table, i.e. enable the truncate on checkpoint option or anything else? Thanks, Hoang
View Replies !
View Related
Searching In TEXT Field
Hi, We are using SQL Server 6.5 and have some TEXT datatype columns in the database which we need to search, based on keywords. Does anyone have experience in using TEXT datatype or can anyone give me some pointers. Also I need to know what is the performance difference between searching thru a text field or searching thru a series of varchar(255) fields, is it easier to search thru the text field by breaking it into a series of varchar(255) character fields or is it okay to search thru the text field. This is urgent.
View Replies !
View Related
Updating A Text Field
I try to update a field of text datatype using WRITETEXT statement. The information that I try to change has both the single(') and double (") quotes in it. How can I get it done ? Thanks Pete
View Replies !
View Related
Splitting A Text Field
hi, I am trying to split a text field . what I want to do is - split a text field and insert chars 1 to 8 in one field and then from 9 to 20 in another and so on. Can someone help me in solving this. TIA. PD
View Replies !
View Related
Concatenate Text In The Same Field
I am new to sql so bear with me here.... This is what I need to do: Field contains business names which can be 1 or more words. Example: Jones Plumbing or American Land Title Association I want to do one of two things to each business name in this column. Either Condense the name to one word if it is two words or less or create an acronym if it is 3 words or more. Example: JonesPlumbing or ALTA Am I correct in thinking I can concatenate Jones Plumbing in this fashion: SELECT REPLACE('ab fgh,' ','') Result being abfgh will the space in the second expression be recognized or will it cause an error? Instead of using an actual name, could I use a var that passes the contents of the field to the first expression and then performs this action? As far as creating acronyms goes... I don't have a clue where to start. doing a word count will give me the ability to do an ifthen statement to direct the function to concatenate the names or acronym it. How do I get it to pull only the first letter of each word? Using Mid() I would have to know the count of all the letters in each field and this is impossible since I have over 1500 fields in the column.
View Replies !
View Related
Insert Text Field
Hellow, Everyone, I am having a problem in Inserting a text field into another Text field on another table. each record in the field might be more than 255 charactors. Any help would be appreciated. Thanks,
View Replies !
View Related
Description Field Is Not Getting The Whole Text
Hi All, I had a datatable in Foxpro where they have a field 'description' with memo datatype which contains more than 4000 byte. I was importing this table into sql server by DTS. After loading the 'description' field has change its datatype into text and it didn't load the whole text into it. I want to load the whole text in SQL server table. Can anyone please tell me how to load this table. It is a very urgent requirement and I will be much appreciated if anyone help me on this ASAP. Thanks.
View Replies !
View Related
Querying A Text Field
This may be easy (hopefully). I am trying to query a text field and filter out based on the number of characters the user has inputed. For example, I only want results returned where the text field has more than 50 characters inputed. Any help is greatly appreciated. Thanks in advance.
View Replies !
View Related
Parsing A Text Field Value Into More Than 1 Value
Hi, My table has 2 fields as 'count' and 'codes'. The 'codes' field has 'count' # of code values in each record. Size of each code is 4. For example, if my record is 2,'abcdefgh' then there are 2 codes and the values are 'abcd' and 'efgh'. Currently I am using 'script component' to parse the field into multiple values. Since I have to read 1 million records and on an average, each record has 10 codes, it is taking hrs to load it. Can it be done without 'script component' using some other transformations? Thanks.
View Replies !
View Related
Text Field Problem
Hi, in MSDN is written reguarding WRITETEXT: data Is the actual text, ntext or image data to store. data can be a literal or a variable. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 KB for text, ntext, and image data. I am tring to insert a text which has around 9000 charcters using WRITETEXT which is less then 10KB, but surprise: after the execution of the sql statement my field remains empty. WHY? I've tested this command and found out that it only accepts 900 characters, which is 995 bytes. I execute this SQL statements sequence: USE RWIN GO SET TEXTSIZE 2147483647 GO EXEC sp_dboption 'rwin', 'select into/bulkcopy', 'true' GO DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(MemoValue) FROM Settings WHERE NR = 19 UPDATETEXT Settings.MemoValue @ptrval NULL 0 'the big string of max 4000 char' GO EXEC sp_dboption 'rwin', 'select into/bulkcopy', 'false' GO Using osql I managed to insert around 8000 characters using the above SQL sequence. But from Visual Studio 2005 using System.Data.SQLClient I managed to insert only 900 characters and after that another update blanks the field.
View Replies !
View Related
Rich Text Field
I currently have a simple project where we need to store paragraphs for letters that will be automatically printed and then mailed to cusomters. These letters will be created based on certain criteria. I have the SQL database set up with two fields...the paragraphCode (which is the key) and then the paragraph. These paragraphs were originally created in Word and some of these paragraphs contain bulleting and other rich text features. I have built a small utility in ASP (using VB .Net) for our intranet to view / insert / edit these paragraphs. If a user is copying and pasting the paragraph from a Word document, what should this paragraph field's datatype be declare as? It is currently NVARCHAR(MAX). Thanks for the information
View Replies !
View Related
SQL View With Text Field
When creating a View, I am able to link the tables togethor and pull the data that I am looking for without a problem. However, when I add a field that is a 'Text' field, I get the following error: ....Column 'dbo.commpay.fdescript' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. If I add it to the GROUP BY clause, then I get the following error: ....The text, ntext and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. This particular field is contains the description of the parts in our inventory table. VIEW Statement: SELECT dbo.armast.fcinvoice AS Invoice, dbo.commpay.fnCalcComm AS Commission, dbo.commpay.fnrate AS CommRate, dbo.armast.fbcompany AS Company, dbo.commpay.fdarpost AS InvDate, dbo.commpay.fpartno AS PartNum, dbo.commpay.fnamount AS Price, dbo.commpay.fsalespn AS RepID, dbo.commpay.fmdescript AS Description FROM dbo.armast INNER JOIN dbo.commpay ON dbo.armast.fcinvoice = dbo.commpay.fcarinv INNER JOIN dbo.slspns ON dbo.commpay.fsalespn = dbo.slspns.fsalespn GROUP BY dbo.armast.fcinvoice, dbo.commpay.fnCalcComm, dbo.commpay.fnrate, dbo.armast.fbcompany, dbo.commpay.fdarpost, dbo.commpay.fpartno, dbo.commpay.fnamount, dbo.commpay.fsalespn, dbo.commpay.fmdescript Any assistance or guidance would be appreciated!
View Replies !
View Related
|
TRACKER
