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 me
because it is a TEXTBLOB (text) field.
Can anyone show me how to write a query that will update all my rows
automatically?
I'm using SQL Server 2000.
Thanks!
4  10864 
Mintyman (mi******@ntlwo rld.com) writes:
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 me
because it is a TEXTBLOB (text) field.
Can anyone show me how to write a query that will update all my rows
automatically?
I'm using SQL Server 2000.
The easiest would almost be to download SQL Express, copy the data
over to a table in SQL 2005, but instead of using text, have a varchar(MAX)
column, on which you can apply rtrim. Then copy back.
To do it in SQL 2000 only, you would have to use UPDATETEXT, and you would
have to work one row at a time. I think you would have to read the last
1000 characters or so, with substring, into a varchar variable and then
write back with UPDATETEXT.
If many of the rows have descriptions shorter than 8000, you could probably
do something like:
col = rtrim(convert(v archar(8000), texttol))
Yet another option that may work is say:
SET ANSI_PADDING OFF
CREATE TABLE #temp (keycol int NOT NULL, textcol text)
Insert data into #temp and update back with UPDATE. When the setting
ANSI_PADDING OFF, trailing spaces are automatically stripped off.
As you see, all options I have presented are fairly complex kludges. The
new MAX data types in SQL 2005 are so much easier to work with.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thanks Erland. I was hoping there would be a nice, easy way to do this :o(
Thanks for the options though, i'll look into the SQL Express option you
talk of. Failing that, i'll try one of the other ones.
"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...
Mintyman (mi******@ntlwo rld.com) writes:
>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 me
because it is a TEXTBLOB (text) field.
Can anyone show me how to write a query that will update all my rows
automaticall y?
I'm using SQL Server 2000.
The easiest would almost be to download SQL Express, copy the data
over to a table in SQL 2005, but instead of using text, have a
varchar(MAX)
column, on which you can apply rtrim. Then copy back.
To do it in SQL 2000 only, you would have to use UPDATETEXT, and you would
have to work one row at a time. I think you would have to read the last
1000 characters or so, with substring, into a varchar variable and then
write back with UPDATETEXT.
If many of the rows have descriptions shorter than 8000, you could
probably
do something like:
col = rtrim(convert(v archar(8000), texttol))
Yet another option that may work is say:
SET ANSI_PADDING OFF
CREATE TABLE #temp (keycol int NOT NULL, textcol text)
Insert data into #temp and update back with UPDATE. When the setting
ANSI_PADDING OFF, trailing spaces are automatically stripped off.
As you see, all options I have presented are fairly complex kludges. The
new MAX data types in SQL 2005 are so much easier to work with.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...
Mintyman (mi******@ntlwo rld.com) writes:
>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 me
because it is a TEXTBLOB (text) field.
Can anyone show me how to write a query that will update all my rows
automaticall y?
I'm using SQL Server 2000.
The easiest would almost be to download SQL Express, copy the data
over to a table in SQL 2005, but instead of using text, have a
varchar(MAX)
column, on which you can apply rtrim. Then copy back.
Or use BCP in character mode, dump out into a perl script, modify, and BCP
back in.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Michael Walton |
last post by:
I am trying to write some code that inserts or updates a text field in my
SQL Server 2000 database. The SQL statement is created using a submit from
a form, and the text is coming from a <textarea>, and therefore being placed
into the SQL statement via a Request("field"). However, due to limitations
in SQL Server 2000 and text fields, I can not use a simple Update or Insert
command with text over 8000 bytes.
Does anybody have any...
|
by: Mike |
last post by:
I'm using a script provided by e-mailanywhere, it's a little too big
for me.
There's 1 text field and 1 password field in a form.
OnSubmit, I would like both fields to be validated to look for invalid
digits (~`!#$%^&*()+=?/). This is not working yet, I don't know why.
In addition, I would like both fields to require from 1 to 12 digits
for submition to continue.
The text field also Lowers Case, can you tell me how to do the same on...
|
by: Roy Adams |
last post by:
Hi I'm reposting this question because for some reason can't post
follow up question to this thread.
What I'm trying to do is put the value and text from a a select in to
a text field and to a hidden field respectfully and the value from
dynamically created hidden fiields in to a text fieldin to a text
field all at the same time
Here's the code as viewed through the browser
<body bgcolor="#FFFFFF" text="#000000" >
<SCRIPT...
|
by: alan |
last post by:
Hello,
can somebody help me with this self written rtrim function?
i do always get a access violation on the line where i want to
terminate the string:
str = '\0'; why can't i acces this specific position?
void rtrim(char *str)
{
for( int i=strlen(str)-1; i>= 0; i-- )
{
|
by: JohnR |
last post by:
When creating an msAccess db within the Access UI itself the fields that are
text are NOT padded with blanks. For example, if I have a 10 char field and
put in "HI" and then when I come back to the field and click my mouse on it
the cursor is just after the "I" in "HI"... that is, no blanks were added
to the field. However, when I create an MDB database programatically in
VB.Net using ADOX and create the tables using SQL stmts (ie:...
| | |
by: pollygw |
last post by:
I have a page that dynamically adds rows to a table and the user can also delete any of the rows in no specific order. When the form is submitted I need to do some validation. I can't loop through it because the names may by out of sequence if the user deleted some lines, so I need to check whether that particular text field exists. For some reason I can't reference the newly created text fields. I tried referencing it by id and by name but...
|
by: dugald.morrow |
last post by:
I have some javascript that updates the text in a text field after
certain actions take place such as clicking a checkbox. The javascript
works fine in Safari and Firefox, but in IE, the text in the text
field remains empty.
The page containing this problem is at the following location:
http://www.skicow.com/component/option,com_skicow/act,shop/Itemid,51/debug_enabled,true
The last parameter enables debug which results in alerts...
|
by: mckurban |
last post by:
Hi All,
I'm not very familiar with Javascript and need help with setting up some javascript code to allow users to create dynamic radio buttons from text field and then to use selected radio value (text string) to appear as firs in the entered field.
I've maneged to get to the dynamic creation part:
<script type="text/javascript">
function update_radio_buttons() {
var companyName =...
|
by: vertigo262 |
last post by:
I want to parse an address from a text field.
for example
textbox data = 123 test street, mountain view, CA 91302
Dim address = Address.text
parse(address)
to
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
