How to RTRIM a Text type field longer then 8000

Question

Post reply

How to RTRIM a Text type field longer then 8000

Henk Schreij

SSCarpal Tunnel

Points: 4554
More actions
October 27, 2009 at 6:48 am

#135886

Trimming a Text column, even when converting it to CHAR(20000) is not possible because RTRIM supports a max of 8000 characters.
Any tips how to truncating all trailing blanks on a Text or NText column?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

There is likely a more efficient way... but this may work:

SELECT REVERSE(LTRIM(REVERSE(yourcolumn)))

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

works fine for me when i use a convert to varchar(max):

--pad my table with too much whitespace

update MyTable set myTextColumn = isnull(convert(varchar(max),myTextColumn),'') + replicate(' ',10000)

--use rtrim to get select without the whitespace

select rtrim(convert(varchar(max),myTextColumn)) from MyTable

--update the table to get rid of whitespace

update MyTable set myTextColumn = rtrim(convert(varchar(max),myTextColumn))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Check the Datalength of that Lowell. I think you need to cast the ' ' inside that replicate to varchar(max) or it'll cap out at 8K.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

ahh your right...you can change to varhcar(max), and make things bigger than 8000, but only in 8000 chunk bites.

here's my test code;

create table MyTable (

MyTableId int identity(1,1) not null primary key,

myTextColumn text)

insert into MyTable(myTextColumn)

SELECT replicate('bananas ',10000) union all

SELECT replicate('apples ',10000) union all

SELECT replicate('oranges ',10000)

select datalength(myTextColumn) from MyTable

--ugg! Garadin is right...it truncates to about 8000 chars

update MyTable set myTextColumn = isnull(convert(varchar(max),myTextColumn),'') + replicate(' ',10000)

--this did make it 16000 chars

select datalength(myTextColumn) from MyTable

select rtrim(convert(varchar(max),myTextColumn)) from MyTable

update MyTable set myTextColumn = rtrim(convert(varchar(max),myTextColumn))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 5

Sorry, the table is a "not owned by myself".

So I can't convert the NText column to a NVarchar(max).

And the 'large value types out of row' is set to off. So the sp can't convert to varchar(max).

I think the double revers gives a handle, but the ltrim will probably stop at 8000 chars.

If I could find a way, probably with patindex, to remove the left spaces, this would be a solution. 😎

Lowell SSC Guru Points: 323518 More actions · Answer 6

Henk Schreij (10/27/2009)
Sorry, the table is a "not owned by myself".
So I can't convert the NText column to a NVarchar(max).
And the 'large value types out of row' is set to off. So the sp can't convert to varchar(max).
I think the double revers gives a handle, but the ltrim will probably stop at 8000 chars.
If I could find a way, probably with patindex, to remove the left spaces, this would be a solution. 😎

Hi Henk; you don't need to alter the tables.

I'm pretty sure if you just need the data trimmed in a stored procedure, you could use the example i posted that just SELECTS the converted value.

select rtrim(convert(nvarchar(max),mynTextColumn)) from MyTable

and i think if you want to fix the data in the table, you could use the update i posted.

update MyTable set mynTextColumn = rtrim(convert(nvarchar(max),mynTextColumn))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Garadin One Orange Chip Points: 29613 More actions · Answer 7

Lowell (10/27/2009)
ahh your right...you can change to varhcar(max), and make things bigger than 8000, but only in 8000 chunk bites.

You can go over 8K if you cast the ' ' inside the replicate:

DECLARE @a varchar(MAX)

SET @a = REPLICATE('abakelfjafa',8000)

SELECT DATALENGTH(@a)-- 7997

SET @a = REPLICATE(CAST('abakelfjafa' AS varchar(MAX)),8000)

SELECT DATALENGTH(@a) -- 88000

SET @a = @a + @a + @a + ' '

SELECT DATALENGTH(@a)-- 264006

SELECT DATALENGTH(REVERSE(LTRIM(REVERSE(@a))))-- 264000

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 8

@lowell & garadin

The combination of your tips did the trick. 🙂

Problem solved.

Thank you. 😎

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 9

Henk Schreij (10/28/2009)
@lowell & garadin
The combination of your tips did the trick. 🙂
Problem solved.
Thank you. 😎

Cool... can you post the code that you ended up with? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 10

Jeff Moden (10/28/2009)
Cool... can you post the code that you ended up with?

Sorry Jeff, it's not possible. 🙁

The problem comes from a complex SP a college was developing for r-trimming all the NText columns in all tables.

He got stuck in part where a RTRIM(CHAR(200000)) was truncated at 8000.

And the test code had an omission where the REPLICATE also was truncated at 8000.

In stead of CHAR(20000) he now uses VARCHAR(MAX) (Solution @lowell)

And the REPLICATE problem was solved by the CAST('...' AS VARCHAR(MAX)),8000) (Solution @Garadin)

But here is the test code:

--drop table MyTable

CREATE TABLE MyTable (

MyTableId int identity(1,1) NOT NULL PRIMARY KEY,

MyTextColumn NTEXT)

INSERT INTO MyTable(MyTextColumn)

SELECT REPLICATE(CAST(N'bananas ' AS VARCHAR(MAX)),10000)+ N'test1'+N' ' union ALL

SELECT REPLICATE(CAST(N'apples 'AS VARCHAR(MAX)), 10000) + N'test2'+N' ' union ALL

SELECT REPLICATE(CAST(N'oranges 'AS VARCHAR(MAX)), 10000)+ N'test3 '

SELECT MyTableId, datalength(MyTextColumn) AS Lng, MyTextColumn FROM MyTable

--1 160018 'bananas .. bananas test1 '

--2 140018 'apples .. apples test2 '

--3 160018 'oranges .. oranges test3 '

UPDATE MyTable SET myTextColumn = RTRIM(CONVERT(varchar(max),MyTextColumn))

SELECT MyTableId, datalength(MyTextColumn) AS Lng, MyTextColumn FROM MyTable

--1 160010 'bananas .. bananas test1'

--2 140010 'apples .. apples test2'

--3 160010 'oranges .. oranges test3'