[thelist] mssql split (You out there Rudy?)

[Brian Cummiskey]

/*---------------------------------------------------------------------
This sounds like a Rudy question to me.  Hope you're listening bud :)
----------------------------------------------------------------------*/

Due to poor data entry controls, I am now dealing with a table which 
contains more than one entry per field, separated by a space, or series 
of spaces.

The good news is that the data is confined to one 'word'.   one element 
of data is formed with -'s

ie:

AAAA-BBBBB-CCCCC  is one element
AAA-BBB-CCC DDD-EEE-FFFFF is a row with 2 elements in it.

So, finding the spaces will be easy, and no worries on word splitting.

I need to split this out into a temp table (to join against later), 
thus, creating single key/value pairs.

I know i need to use a charindex on it to find the space, and then grab 
the left and right pieces.

Icing on the cake, is that some have 3 or even 4 groups in one row.  
Some may even have more that I haven't found yet, so this needs to be 
some sort of loop.  After the initial split, i need to test the RIGHT 
portion of the split again, and if necessary, split again and re-test, 
and i'm not sure on the proper (read: a working) way to do this


Any help will be much appreciated.
Thanks!


Now, excuse me while I go break the vending machines in the data entry 
department :D


DBMS: MSSQL 2000