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") = vbcrlf & RTrim(DTSSource("temp_text"))
      Main = DTSTransformStat_OK
End Function
AirborneloveAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
If in fact they are vbCrLf's and not anything else vbCr or vbLf than use something like the following:

Function Main()
     DTSDestination("TempText") = vbCrLf & Trim(Replace(DTSSource("temp_text"), vbCrLf, ""))
     Main = DTSTransformStat_OK
End Function
0
 
lluthienCommented:
the trick is that you cannot do a whole lot with NTEXT fields.
at least not easily in sql Server.

do you need to work with text fields, or can you change it to nvarchar?
0
 
ewahnerConnect With a Mentor Commented:
To remove spaces from the beginning and the end of a string you need to use "Trim" instead of RTrim.  RTrim just removes trailing, not leading.

Function Main()
     DTSDestination("TempText") = vbcrlf & Trim(DTSSource("temp_text"))
     Main = DTSTransformStat_OK
End Function
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsCommented:
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.
0
 
AirborneloveAuthor Commented:
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?

Æ
0
 
AirborneloveAuthor Commented:
TEST
0
 
AirborneloveAuthor Commented:

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!

Æ

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.