473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Use RTRIM function on TEXTBLOB (text) field

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!
Feb 21 '07 #1
4 10835
Mintyman (mi******@ntlworld.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(varchar(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****@sommarskog.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
Feb 21 '07 #2
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****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Mintyman (mi******@ntlworld.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(varchar(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****@sommarskog.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

Feb 21 '07 #3
Mintyman (mi******@ntlworld.com) writes:
Thanks Erland. I was hoping there would be a nice, easy way to do this :o(
Anything easy with the text data type? Gee, you must believe in Santa Claus
too! :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
Feb 21 '07 #4
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Mintyman (mi******@ntlworld.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.
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
Feb 21 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
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...
5
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...
3
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...
9
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...
2
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...
3
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...
3
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...
2
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...
1
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
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.