Can You Parse Out A Text String &> 8000 Chars Long??
Mar 30, 2005
Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
Hi there, I'm having a problem when I insert a string (from C#) which is 167 characters long. The field in SQL Server Express is a varchar(250), but the string gets cut of somehow at 150 characters.
Why does this happen?
The table and stored proc I use are defined like this:
USE [Test] GO /****** Object: Table [dbo].[tblImage] Script Date: 05/18/2006 10:42:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblImage]( [imgID] [bigint] IDENTITY(1,1) NOT NULL, [persID] [bigint] NOT NULL, [imgPad] [varchar](250) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
Hi, I have a problem with a text string which is more than 8000 chars. I am taking this string as an input from an application.so,I cannot define a local variable as text or ntext and varchar has limitation of only 8000 char. Can anyone help me in dealing with this situation. Also,I cannot break the string at application level.I wish if I could solve it at db level somehow? TIA pd
hi all, I'm after a way to produce a single-lined output of a char/varchar string replicated over 8000 times.
basically I've been given a task to create a stored procedure which can accept any integer to replicate a string X times.
From what I've read the replicate() fn will covert to varchar of max 8000 bytes.
I've thought about creating a UDF to accept the varchar and int and run a loop to keep outputting but 'print' will pass an endline to the buffer which is no good for my loop.
Books Online gives a way to send a message larger than the VARCHAR max of 8000 chars, but the @query argument to xp_sendmail is a simple text string and my data is much more complex, and formatted. Also BOL shows an example using a temporary text file, but it is not clear precisely how you write your insert statements. I tried the following, which writes out all the data and sends it ok except, after each row, there is about a page of blank spaces. What is wrong with my syntax?
SET LANGUAGE British GO DECLARE @msgstr VARCHAR(80) DECLARE @cmd VARCHAR(80) DECLARE @PMID INT DECLARE @forename VARCHAR(30) CREATE TABLE ##texttab (c1 text) SET @msgstr = 'THE FOLLOWING QUOTES ARE CURRENTLY MARKED AS PENDING:' INSERT ##texttab SELECT @msgstr DECLARE C2 CURSOR FOR SELECT ProjMgrID FROM surdba.SVY_QUOTES WHERE StatusID=6 OPEN C2 FETCH NEXT FROM C2 INTO @PMID WHILE @@FETCH_STATUS = 0 BEGIN IF @PMID > 1000 SELECT @forename = ISNULL(Forename,' ') FROM surdba.SVY_PERSONNEL_GENERAL WHERE EmployeeID = @PMID ELSE SET @forename = ' ' INSERT ##texttab values (RTRIM(@forename)) FETCH NEXT FROM C2 INTO @PMID END CLOSE C2 DEALLOCATE C2 INSERT ##texttab values ( ' - This information is autogenerated from the Survey database.') SET @cmd = 'SELECT c1 FROM ##texttab' EXEC master.dbo.xp_sendmail @recipients = 'Robin Pearce', @subject = 'ALL PENDING QUOTES', @query = @cmd, @no_header = 'TRUE' DROP TABLE ##texttab GO
Would appreciate any help on this one, I do not have time to learn HTML, thanks Robin Pearce
I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??
SELECT '510', PRODCLASSID , '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid, 'Desc1' = CASE WHEN SUBSTRING(externalid,1,2) = 'MF' THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'MP' THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'PF' THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'PP' THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. ' END + CASE WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. ' END + 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.' + CASE
WHEN prodclassid IN ('P1','P11','P8','P9') THEN ' (mileage limited to 300 km)' WHEN prodclassid IN ('P7') THEN ' (mileage limited to 200 km)' ELSE NULL END + SPACE(5000 - LEN('Desc1')) .......
I need to handle this conversion in SSIS and not on oracle.
The following expression is executed on a datatype of dt_str with a length of 8000.
SUBSTRING((DT_STR,8000,1252)Column_name,1,8000)
Records longer then 4000 bytes take an error path
The next expression with 4000 bytes works but there is truncation.
SUBSTRING((DT_STR,4000,1252)Column_name,1,4000)
Basically I need to know how to cast a text or ntext into a varchar or nvarchar using ssis but I need to capture the first 8000 byes without truncation.
is this possible?
Using SSIS Reading From oracle I can convert to a text or ntext field but I am having a hard time going directly to a varchar.
I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.
If I try to use text datatype..It doesn't support rtrim, stuff functions etc...
So could any one suggest me a best way to save a long string without any restriction of size...
My front end is C#.Net and Back End is SQL SERVER 2000
I am using SQL Server Compact Edition and I get an exception when running a query with a LIKE-operator which parameter is a string with length greater than 256 characters.
Has anybody had this problem? Any ideas on how to fix this?
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>
I am trying to change a text field into a varchar 8000. I get his error message when trying to convert.
Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8317 which is greater than the allowable maximum of 8060. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Anyone know of a way to either truncate the text field or to select only those that are over the 8000 character mark?
I wrote this sql function which takes a comma seperated string of numbers, splits the numbers seperately and stores it in a table. I have specified the input parameter type as text instead of varchar, the size of the string can get more than 8000.
But the function is not working properly if the input size is more than 8000. For example if the input string is of length 8005 and this is the input string from 7995 to 8005 - '123,124,125'. It works fine till 123 and after that it throws an error, Syntax error converting the varchar value '124,125' to a column of data type int. Can anyone tell me what is wrong with this. I am using string functions like charindex, substring. I can post the full function if you want.
How do you insert more than 255 characters into a text field? or am I using READTEXT and DATALENGTH incorrectly?
I am trying to concatenate two 255 varchar strings. Both do have 255 characters of data. I want to insert the concatenated strings into a field define as a text data type. if I try to ' insert into tempTbl(textFieldDataType) values (@text1+@text2) it appears only the @text1 is inserted. I have been told the data is really there, but I can only read the 256 position + with READTEXT.
DECLARE @textptr varbinary(16) select @textptr = (select TEXTPTR(textFieldDataType) from tempTbl where pktempTbl = 33) READTEXT tempTbl.textFieldDataType @textPtr 255 10
This should have read 10 text positions after the 255th position. However, I get msg Msg 7124, Level 16, State 1 The offset and length specified in the READTEXT command is greater than the actual data length of 255.
If I use the datalength function, it appears to confirm that @text2 was never inserted
declare @length int select @length = (select datalength(textFieldDataTypet) from tempTbl where pktempTbl = 33) select @length
Hi all,I have a variable called Description which is set from a database field - How do i set another variable to grab the first 200 chars from this var? hope this makes sense
CREATE TABLE saved_query ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(32) NOT NULL, query_text VARCHAR(2048) NOT NULL )
CREATE TABLE saved_query ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(32) NOT NULL, query_text VARCHAR(2048) NOT NULL)
and whenever I insert or update the query_text field, it's always truncated to 255 characters. Why? I clearly specified a larger field size. I also tried defining query_text as a "TEXT" but got the same result.
I'm running SQL Server 2K (sp3a) and when I run the following query (inquery analyzer):SELECT id, LEN(ForeignWord) as Length, ForeignWordFROM WordsWHERE Language ='Thai' and LEN(ForeignWord) > 300ORDER BY Length descI receive the following results:id Length ForeignWord------- ----------- -----------34756 445 เ#...truncated at 25534839 412 เ$...truncated at 25537613 350 โ#...truncated at 25537808 315 โ#...truncated at 25538140 315 โ#...truncated at 255(The ForeignWord field is defined as varchar(1000))Note that even though the server says that the lengths are in excess of255 characters, the results are all truncated at 255 characters.I read that SQL Server 6.5 and 7.0 had some issues related to nativeaccess versus OleDb or ODBC, but this is Server 2K patched with thelatest updates.Any idea why my varchar fields are being truncated?-- RickHere is my table definition:CREATE TABLE [Words] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[Language] [varchar] (50) NOT NULL ,[Module] [int] NOT NULL ,[Lesson] [int] NOT NULL ,[EnglishWord] [varchar] (1000) NOT NULL ,[ForeignWord] [varchar] (1000) NOT NULL ,[Note] [varchar] (2000) NULL ,[Military] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Military]DEFAULT (0),[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Supplemental] DEFAULT (0),[SoundFileName] [varchar] (1000) NULL)
Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))
Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn
Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....
Here is the sample string: MFD-2fdr4.zip
CREATE FUNCTION Parse(String, '-' , '.') ..... .... ..... END
I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them. The format of the name field is 'AAAA-BBBBBB-123' here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id. I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?
SELECT id AS 'ID', name AS 'name', sequence AS 'num' FROM FirstTable A INNER JOIN SecondTable q ON (CONVERT(INT,RIGHT(name,3))= a.id) INNER JOIN ThridTable t ON(t.id = q.id) INNER JOIN FourthTable s ON (q.name = s.name ) WHERE A.id = @ID AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1)) ORDER BY 'ID','num'
One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?
I have a table in SQL Server 2000 which has few triggers. When I try to update a record in that table, I get following message:
Warning: The table '[TABLE_NAME]' has been created but its maximum row size (17275) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
where the data I am updating is well less than the field limit. The triggers are stored in syscomments table and when I dropped these triggers, the update statements were executed without this message. Is there anyway I can change the data type of field text in syscomments from nvarchar to ntext?
We had a sql 2000 db that had full text implemented on it. We upgrade that server to a 2005 server and now the full text searches that once worked don't. Most do work just the ones with ; or special characters in the query string don't work.
Anyone have the same behavior and how to get it to wrok again?
Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005! I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string. I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string to @Insertcontent , the stored procedure can't be launch! why? create procedure Hellocw_ImportBookmark @userId varchar(80), @FolderId varchar(80), @Insertcontent nvarchar(max) as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
as declare @contentsql nvarchar(max); set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+ @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'') where userId='''+@userID+''''; exec sp_executesql @contentsql;
In MS Access 2000 if I have a String such as:Column1Delta CC: 123Charley CC: 234Foxtrot CC: 890and I wanted to extact just the numbers in to a field called CCI could use this formula in a calculated field:CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)resulting in:CC123234890Any idea on what the code should be within a view in SQL Server?also -- what is a good reference that can help with these types ofproblems.Any help appreciated!RBollinger
This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2
My current stumbling block is:
One of the fields in table1 is a free format address field (address). eg. could be (street addres, city prov, postal) or could be (street address 1, address2, address 3, city prov, postal)
I want to be able to assign individual components of the address to corresponding fields
if # of commas = 2 then address1 = substring(address,1, position of first comma) cityprov = substring(address,possition of first comma, position of second comman) postal = substring(address rest of field)
I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...
ANy ideas on how best to accompish this? ( this table is needed for some leacy software which can only handle one record with all infor....
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
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
