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

Brian Cummiskey brian at hondaswap.com
Tue Feb 27 23:56:01 CST 2007

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


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.

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


More information about the thelist mailing list