String Manipulation

[edwardturner]

Hi,

I need to use DTS to place the first 10 characters of a string into one field the then remaining characters (from 11 onward) into another field.

In SQL there is the substring function, what is the equivalent in DTS? I am using a data pump task to do this so it would be the VB script command.

Thanks.

 

[mmaz]

You can use the vbscript Left function:
newString = Left(oldString,10)

 

[edwardturner]

Thanks, the left function works and it does give me the first 10 characters. I then needed to get remaining characters and place them into a string. I used right(oldString, 10) but this did not give me all the characters to the right of the 10th character. The right function does not seem to look at whitespace. In the example below:

21 SCHOOL LIBRARY


right(oldString, 10)

Gives OL LIBRARY and not SCHOOL LIBRARY. It seems to take everything 10 characters to the right of the '1' character. Is there a function that will accept whitespace as a valid character so that I can actually get the string from the 10 character onward?

 

[mmaz]

The Right function starts at the end of your string and goes backwards. That's why you're getting OL LIBRARY...

To get SCHOOL LIBRARY, you can use the function Mid(string, start, length) like this:

newString = Mid(oldString, 10, Len(oldString))