Airbornelove
asked on
How to Remove Leading/Trailing Spaces of NText datatype Text
Hi All,
I am using DTS to import data from one table to another. But I want the 2nd table to contain the same text minus the leading/trailing spaces of the text from the first table. The fields have datatype NTEXT. RTrim/LTrim doesn't seem to work. What's the trick?
Here's my Transformation Script:
Function Main()
DTSDestination("TempText")
Main = DTSTransformStat_OK
End Function
I am using DTS to import data from one table to another. But I want the 2nd table to contain the same text minus the leading/trailing spaces of the text from the first table. The fields have datatype NTEXT. RTrim/LTrim doesn't seem to work. What's the trick?
Here's my Transformation Script:
Function Main()
DTSDestination("TempText")
Main = DTSTransformStat_OK
End Function
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
lluthien,
>>the trick is that you cannot do a whole lot with NTEXT fields.<<
While this is true if using T-SQL the questioner is using VBScript in DTS, so a (n)text is treated as a string and the regular VBScript functions can be used. In this case Trim(). Unfortunately what I suspect is happening is that what the questioner sees as spaces are in fact unprintable characters such as vbCrLf which cannot be removed with Trim.
>>the trick is that you cannot do a whole lot with NTEXT fields.<<
While this is true if using T-SQL the questioner is using VBScript in DTS, so a (n)text is treated as a string and the regular VBScript functions can be used. In this case Trim(). Unfortunately what I suspect is happening is that what the questioner sees as spaces are in fact unprintable characters such as vbCrLf which cannot be removed with Trim.
ASKER
Hi acperkins,
Yes you are right :) Some are spaces but most are vbCrLf. I just realized it when you mentioned it. In this case, how can a leading vbCrLf be removed?
Æ
Yes you are right :) Some are spaces but most are vbCrLf. I just realized it when you mentioned it. In this case, how can a leading vbCrLf be removed?
Æ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
TEST
ASKER
I increased the points so I can give the initial worth of the question (which was 125) to acperkins for the solution to my problem, and at the same give ewahner credit for answering my initial question. I think the Title of my question needs to be changed too but I don't know how.
Thanks everyone! Cheers to the Experts!
Æ
at least not easily in sql Server.
do you need to work with text fields, or can you change it to nvarchar?