Need sql help again... breaking string into 2 piece

[rsutoratosu]

Trying to figure how to split a text string (addresses) from 1 big 100 maxcharacter into 2 50 character max columns. Basically the string is an address line, no delimits, has spaces only. So need to split up to 100 into 2 without knowing where the spaces are. I found some sample codes that I tried to use without success.. this is sql 2014, so it doesn't have the new split string feature (I been told).

Been trying to figure this out for a week now.. kinda giving up. If any one has suggestions.. I tried to find the code I originally found online but lost the website.

It does something like reverse (string)
string - (half);
reverse string - (half).

I'm not really sure but ill try to find it.

 

[rsutoratosu]

I think i figure it out, just waiting for the server to be available so I can test again 1000 lines instead of 5 lines..

 

[mike8675309]

Here is a basic logical construct, yet you may not be able to split it exactly into two 50 character strings if you want it split on space because the data may not allow that.

Take the string, use the replace statement on it to replace all the spaces with an empty string.
Take the length of the output of that and subtract it from the length of the string with its spaces.
You now know how many spaces are in the string.
Half of that number is where you would split.
Talke the Half (round up or down based on your expectation) and use the CHARINDEX command to find the Nth space in the string
Use that result in your SUBSTRING of the string.

If it has to be exactly 50, just take the string and use substring to pull it apart into 2 pieces.

 

[ptthomas]

I think i figure it out, just waiting for the server to be available so I can test again 1000 lines instead of 5 lines..

Well what do you know.