[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Remove Leading/Trailing Spaces of NText datatype Text

Posted on 2006-05-04
7
Medium Priority
?
2,442 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:Airbornelove
7 Comments
 
LVL 11

Expert Comment

by:lluthien
ID: 16603966
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
 
LVL 2

Assisted Solution

by:ewahner
ewahner earned 80 total points
ID: 16604907
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16608220
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Airbornelove
ID: 16611802
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 16611821
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
 

Author Comment

by:Airbornelove
ID: 16612030
TEST
0
 

Author Comment

by:Airbornelove
ID: 16612056

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question