I did all that to no avail.

I ended up doing the following which ain't purdy, but it works.

SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1))) + 1,50)),"...

I did the following workaround that solved the problem of it ignoring references to address_1 and address_2 in address_2 and address_3. It ain't purdy, but it works.

SUBSTRING(Street,LEN(SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50))," ",1)))...

It appears to be ignoring the references to address_1 and address_2 in address_2 and address_3.

I found the problem. It was where the " " was towards the end of address_2. It needed to be moved over a flew places from where it was.

It WAS...

CRAP!!! We're are right there and I don't see the problem either. I've matched every paren and they all match up unless this mess has made me go blind.

Alvin Ramard (6/10/2015)FINDSTRING and CHARINDEX don't work the same way. 😉

Try:

Address_1:

SUBSTRING(Street,1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,1,50)), " ",1))

Address_2:

SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(REVERSE(SUBSTRING(Street,LEN(address_1) + 1, " ",50)),1))

Address_3:

SUBSTRING(Street,LEN(address_1) + LEN(address_2) + 1,50 -...

ok, this is what I have now and it does run. but the data is weird:

Address_1:

SUBSTRING(Street,1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,1,50)),1))

Address_2:

SUBSTRING(Street,LEN(address_1) + 1,50 - FINDSTRING(" ",REVERSE(SUBSTRING(Street,LEN(address_1) + 1,50)),1))

Address_3:

SUBSTRING(Street,LEN(address_1) + LEN(address_2) + 1,50...

Yes.

It doesn't like address_2 and address_3. Those are actually what the derived fields are named: Address_1, address_2, address_3. The primary field is called Street. Here's how I modified yours with...

OK, so for street2, I used the following and got no errors. I'm not sure how to code street1 or street3 though. Or actually, reading the code below, it looks...

In addition to my previous reply, i did just discovered that the equivalent to charindex in a derived column is findstring.

Ahhhh, if it were only that easy. Actually, I did inquire why I couldn't simply use SQL and the reply was basically a...

Thanks Alvin!

My supervisor-imposed requirement is that I NOT use SQL to do the conversion and to use some alternative method inside the package. Obviously SQL is the most simple way...

David, you jumped WAY over my head with that one!

OK, so a .NET script, I'm up for that. However, I don't know .NET. Anybody willing to write the script...

David, this solution will somewhat work, but doesn't exactly do what I need and I'll try my best to explain why:

Since you're using substring for position 1-50, 51-100 and 101-150,...