January 23, 2009 at 7:39 am
I have a text type field that exceeds 8000 chars. it is fed by a Memo box out of a propriatry program. the text contains char(10), char(13), and 1000s of extra spaces in between sections of text.
I have thought of using a function to loop through it and find the non space chars and add them to a variable. but without a way to determine the end of the field the loop would run infinetly .
Len won't work on a Test data type nor will replace. CharLength doesn't give a valid length either.
any ideas?
January 23, 2009 at 9:36 am
You asked for any ideas? Hopefully this might start your tbinking along a different track to a solution.
If this is new work, that is the column definition in your database table can be changed to VARCHAR(Max) why do you want to remove all the space (blank) characters?
Please note that:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. s.
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes.The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length.
2^31 = 2,147,483,648 bytes for non-unicode characters each character can be represented in a byte (unicode each character requires 2 bytes)
Further The built-in string functions that can operate on character and binary data are enhanced to support large-value data types as arguments. These functions include the following:
COL_LENGTH, CHARINDEX, PATINDEX, LEN, DATALENGTH and SUBSTRING
Is your goal to simply reduce storage space required or is it something else?
January 23, 2009 at 11:16 am
since a third party software is invloved, and you can't change the datatype, I have just the tool for you.
I've used this to Find-And-Replace within text fields lots of times...for example, TEXT data containing web page content, where i needed to replace relative links for fully defined http:// type links.
it might be slow, depending on your table, but replacing something like {space}{space}{space}{space} with just one {space} will get er done it sounds like:
[font="Courier New"]
--this loops thru ALL ROWS In your entire table, and replaces @WhatToFind with @ReplaceWith
--find and replace the following
--reviews the table containing the Text field
--reviewid the PK of your Table containing the Text field
--review_body with YOUR TEXT field
DECLARE @WhatToFind VARCHAR(30),
@ReplaceWith VARCHAR(30)
SET WhatToFind =' '
SET ReplaceWith =' '
DECLARE @reviewid INT, @ptr BINARY(16), @idx INT
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%' + @WhatToFind + '%',review_body)-1
FROM reviews
WHERE PATINDEX(@WhatToFind,review_body) > 0
WHILE @reviewid > 0
BEGIN
SELECT @ptr = TEXTPTR(review_body)
FROM reviews
WHERE reviewid = @reviewid
UPDATETEXT reviews.review_body @ptr @idx 13 @ReplaceWith
SET @reviewid = 0
SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX(@WhatToFind,review_body)-1
FROM reviews
WHERE reviewid > @reviewid
AND PATINDEX('%' + @WhatToFind + '%',review_body) > 0
END
[/font]
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply