Text Doesn't Support Rtrim,ltrim Etc Functions...which Is Other Better Way To Save A Long String
Nov 10, 2007
Hi
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 have some data that contains spaces both before and after the text string, and now I'm wondering what would be the best method to remove these blanks (sometimes there are no blanks, so I can't check with a specifik width)?
Is it possible to do something like:
set foo = ltrim(rtrim(foo))
or do I have to split it into 2 steps?
This trimming will be done in update & insert statements
You all have been so much help, but I've discovered yet another problem. I'm trying to clean up my table using the following command: UPDATE dbo.TableName SET First_Name = LTrim(RTrim(First_Name))But it does not seem to have any effect. Thoughts? Thanks!
I'm not sure about why I'm not able to remove spaces even after trimming them. Below is the result of query I'm usning.
select distinct LTRIM(RTRIM(Promotion_Code)) Promotion_Code --, count(Promotion_code) from dbo.Marketing_Promotion_Tb where Promotion_code like '%1BTPIZZA%'
USE [Analytical] GO /****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DailyTickMinMaxAnalysis]( [symbol] [varchar](50) NOT NULL, [cDate] [varchar](50) NOT NULL, [part] [varchar](50) NOT NULL, [collection] [varchar](max) NULL, CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED ( [symbol] ASC, [cDate] ASC, [part] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
Stored procedure is:
USE [Analytical] GO /****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO
ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis] @symbol varchar(50), @cDate varchar(50), @part varchar(50), @collection varchar(50) = NULL AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.DailyTickMinMaxAnalysis ( symbol, cDate, part, [collection]) Values ( @symbol, @cDate, @part, @collection) END
C# procedure: public void saveHistTickMinMaxAnalysis ( int tableIndex ) { if ( tableIndex != -1 ) { using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\sqlexpress;" + "database=Analytical;Trusted_Connection=Yes" ) ) { SqlCommand cmdm = new SqlCommand ( ); conn_Analytical.Open ( ); cmdm.Connection = conn_Analytical; cmdm.CommandType = CommandType.StoredProcedure; cmdm.CommandText = "InsertDailyTickMinMaxAnalysis"; cmdm.Parameters.Clear ( ); cmdm.Parameters.Add ( "@symbol", SqlDbType.VarChar, 50, "symbol" ); cmdm.Parameters.Add ( "@cDate", SqlDbType.VarChar, 50, "cDate" ); cmdm.Parameters.Add ( "@part", SqlDbType.VarChar, 50, "part" ); // 2 cmdm.Parameters.Add ( "@collection", SqlDbType.VarChar, 500, "collection" ); // 3 foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows ) { cmdm.Parameters[0].Value = ( string )row[0]; cmdm.Parameters[1].Value = ( string )row[1]; cmdm.Parameters[2].Value = ( string )row[2]; cmdm.Parameters[3].Value = ( string )row[3]; Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed try { cmdm.ExecuteNonQuery ( ); } catch { } } } } } // saveHistTickMinMaxAnalysis
Table is defined with the last columns[3].Length = 500;
I have a question that has a co-worker and myself confused. We are using the SQL Server Enterprise Manager. When I select return all rows in my tables, any content that is too long will appear as <Long Text> on my co-workers machine, but the column is blank on my machine. Is there a property or configuration that enables/disables <Long Text>? I am confused as to why it appears on other machines, but not mine.
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)...
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.
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')) .......
Hi all, We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case
select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"') even though there exist records that satisfy the condition in the table, the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"
If I remove the last 1 from the search string i get lot of records, Can anybody help me out.
Hi,I have erronous white space at the end of my 'description' field within my'product' table. I tried using the RTRIM function but it won't let mebecause it is a TEXTBLOB (text) field.Can anyone show me how to write a query that will update all my rowsautomatically?I'm using SQL Server 2000.Thanks!
Hi,Do any versions of SQL Server support the following functions, asthey appear in the Oracle Database:-1) XMLElement2) XMLAttributes3) XMLForestThanks in Advance for your replyByeAmardeep Verma
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
So i am trying to make a simple database and with one table and use a gui to make rows. When i run the program in debug mode it creates a new copy of my DB in the debug folder and saves only their, i cannot find a way to save back to the original DB no matter what i do including changing the Copy to Output Directory.
Now the more i try to fix this the more new DB it creates one in the Release Folder, one in the bin/Debug/bin/Debug one in bin/Release/bin/Release.
I am going crazy trying to scoure the entire project looking for someplace i can fix this.
All i want is to create a DB and be able to make changes to it and only it.
Hi,I have a problem to insert(update) a long text (more than 64K) intoSQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.MSDN says: "When the ntext, text, and image data values get larger,however, they must be handled on a block-by-block basis. BothTransact-SQL and the database APIs contain functions that allow applications towork with ntext, text, and image data block by block." Could somebodygive me an example how to do this, please.Thank you
This question has been asked a lot, without a definitive answer. Can you tell me if SQL Server 2008 will support rich text. I believe the November CTP does not have this functionality. It is a pretty important decision regarding my organization's adoption of the new product. Thank you Jason
I have a challenge and am not very versed with coding this situation. The situation is breaking up an address into separate portions: number, number suffix, direction, street name, thoroughfare, street suffix, etc. I am having troubles getting started, could anyone point me somewhere in the right direction with a little guidance or has anyone done this and have the code stored? This is not my strong point and have been trying to improve, sorry. D--
Dear All,Access adp on sql-server 2000After upgrating to A2003 updating data with 1 perticular combobox causes theprogram to hangs without any error-msg.Traying to change te combobox recordsource i get this error:This version of Microsoft Access doesn't support design changes to theversion of Microsoft SQL Server your project is connected to. See theMicrosoft Office Update Web site for the latest information and downloads(on the Help menu, click Office on the Web). Your design changes will not besaved.The solution in :http://support.microsoft.com/defaul...kb;en-us;313298tolks about SP 'dt_verstamp007' but I have SP 'dt_verstamp006'What should I do.Is the failure of the combobox also caused by the absence of dt_verstamp007???Filip
My SQL statement: SQL Server seems to think my SQL Statement is too long........ I'm not sure why. "INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,DateInitiated) select 'Benjamin Short' ,'6' ,'2' ,'Printer' ,'40' ,'' ,'';" Error message: The identifier that starts with 'INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,' is too long. Maximum length is 128.
I have a challenge and am not very versed with coding this situation. The situation is I am trying to put long text (more that 255 char) in a ntext field in SQL7, but the max char can input is only 255. Anyone can advice on how to put in more that 255 char in a field as well as how to select back the record whice more than 255 char.
Please give advice if you encounter this problem before.
Anyone have same sample on how to put in and retrive long text (more then 255 characters). Please Comment.
------------ Ray Miao at 7/19/00 8:24:08 AM
You should use readtext and writetext to access text column. By the way, nchar and nvarchar can hold up to 4000 characteres.
------------ Wong at 7/18/00 11:43:15 PM
I have a challenge and am not very versed with coding this situation. The situation is I am trying to put long text (more that 255 char) in a ntext field in SQL7, but the max char can input is only 255. Anyone can advice on how to put in more that 255 char in a field as well as how to select back the record whice more than 255 char.
Please give advice if you encounter this problem before.
How long exactly is the text datatype in non-unicode characters for SQLS7? I guess I need to find out how long my input is and break it up into multiple text fields, but what's the max length?
I have tranferred my database from Access to SQL. In some cases where there is a very long text in the field of "content" (thats the name of the field) appears the sign <Long Text> while in some other fieldsof the "content" the text appears normally...
I have used ntext 16 as a datatype... How can i dissapear this sign from the field...is there any way?? Thank you
