Desparately need help parsing field

Hello,

I have to split data that is currently in a single field delimited by a space.

Problem is the field could be between 7 and 72 bytes long.
I have to parse the field for a character set and its corresponding number.
For instance: MyColumn = Index1 50 Index2 40 Index3 10

I need to parse this out to:

INDEX PERCENT
-------
Index1 50
Index2 40
Index3 10

Again, there may exist 1-4 sets of this data within the field.

Anyone have a stored proc or better T-SQL ?

Thanks so much in advance!

mzimmIs this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?|||Originally posted by rnealejr
Is this a 1 time deal or will it repeat ? How many records exist ? How is the data currently stored ? What are the different combinations of datasets - please provide examples ?

Hello,

This will repeat many times.
Upwards of 1000 records exis t in this table.
The datasets will be in combination of
(Example) Index1 5 Index2 10 ... Indexn ##
where Indexn can be any number of characters long and ## is between 1 and 100. The characters and numbers are separated by a space.

Actual example:

Column1 = Lehman 100
Column2 = lehIUSGV 5 LehGVcr 10 MSCIEAFE 5 R2000Gro 5 RMCAPGro 10
Column3 = LehCredt 10 Lehmktgov 15 DnB 35 S&P 40

Thanks much for your help!

mzimm|||These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?|||Originally posted by rnealejr
These records can have 1 combination of string and number up to n strings and n numbers. You reference the records as columns - how many columns exist - or are these actually records. Can you provide a snapshot of this table in em or qa and post it to help clear up the database design question ?

I GREATLY aplogize, it's late.
Those are RECORDS, only 1 column.|||This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.|||Originally posted by rnealejr
This is the first solution that came to mind (so there might be better ones). Use dts to "export" the data to a delimited file - use vbscript to transform the data (basically breaking up the space delimited records into sets of individual lines based on the spacing sequence). Then import that file into sql server into the appropriate table. If you need the vbscript for the dts part let me know and I will post.

Yes, Please, can you post the VBscript?
It would be thee answer to my problem.

Thanks again.|||Here you go (Let me know if you need additional help):

Function Main()
strString = DTSSource("b")
intTest = 1
intBegin = 1
while intTest <> 0
intTest = instr(intTest , strString, " ")
strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & " "
intTest = intTest + 1
intBegin = intTest
intTest = instr(intTest, strString, " ")
if intTest = 0 then
strFinal = strFinal & right(strString, len(strString) - intBegin + 1) & " "
else
strFinal = strFinal & mid(strString, intBegin, intTest - intBegin) & vbCrLf
intTest = intTest + 1
intBegin = intTest
end if
wend
DTSDestination("b") = strFinal
Main = DTSTransformStat_OK
End Function

Design -- Should this be split up into a few tables?

I'm grappling with this design problem right now:

I have a table of users. Every user has an e-mail address and (hashed) password. Some of those users work for a company, and some of them do not. Of those who do not work for a company, some are salespeople who sell to one or more companies. Some users are simply administrators who don't work for a specific company. So here's what my users table looks like right now: "UserID, Email, Password, CompanyID (Nullable), IsAdmin"
And here's my companies table: "CompanyID, CompanyName, SalespersonID"

Of course, I could separate it out and make a Users table, an Employees table, and a Salespeople table. The way the relationship works out, though, I could use the same ID number for all three tables, and that indicates to me that perhaps they all belong in the same table. It seems silly, after all to have a Salespeople table whose only field is "UserID."

Two factors of the first design concern me: First is the fact that a salesperson could also have a company. I guess I could write a check constraint to prevent this, but doesn't having the companyID in the Users table violate a normalization rule? Maybe? The second is the fact that the Companies table relies upon Users, which in turn relies upon Companies. In OOP, this usually isn't a good thing, but I'm not sure whether it's cause for concern in a relational database.

Anyway, I really don't know what I should be doing with this design. Any suggestions?

Thanks in advance,
-StarwizAnd here's my companies table: "CompanyID, CompanyName, SalespersonID"this suggests that each company can have one and only one salesperson at any given time

sounds to me like you really need a usercompany table

primary key would be composite: userid plus companyid

that way, a user can be a salesperson for more than one company, and, more importantly, a company can have more than one salesperson

the company table would just have companyid and companyname