Text what?

Posted on: November 12, 2010 Posted by: John Huh Comments: 0

Text what?

Problem with datatype “text” is… you can’t search and replace a string using the REPLACE function.
One way is to cast the text field to a varchar/nvarchar to use the REPLACE function.

Example:

[sourcecode language=”sql”]
UPDATE tablename
SET textfield = REPLACE( CAST(textfield AS VARCHAR(8000)), ‘\r\n’, ” )
[/sourcecode]

Also, len(textfield) will generate an error and won’t return the length of it.

[sourcecode language=”sql”]
SELECT DATALENGTH(textfield) AS sizeoffield
[/sourcecode]

will, however, return the length of the “textfield”.

Leave a Reply:

Your email address will not be published. Required fields are marked *