[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
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
More information about the thelist
mailing list