Question

Locked

Trying to seperate info from a field into different parts

By mgottberg ·
I am working on a project for Special Olympics in which the file structure of their Games Management Software is to say rather bad. I know enough to be dangerous using SQL. What I would like to do is take the following from a table called dbo.entries in field called misc

"FDV:50MDASH-08FS:14.8gamesScratched:NintLastChanged:39573.5706486227Lan:2PLC:4QS:16.6"

and seperate into this

FDV:50MDASH-08 (This is event and the division)
gamesScratched:N (Have they scratched)
Lan:2 (Lane they are assigned to)
QS:16.6 (Qualifying Score)


The keys are FDV: , gamesScratched: , Lan: , QS:

The size of field can vary alot - it has a format of ntext and may contain other items

The size after FDV and QS can vary, but Lan and gamesScratched should only be 1 character - Lan may not be present depending on event - ie field events wouldn't have it

If I can get this I think I would be able to make a web interface to see directly into the db (hosted on a SQL 2005 Srvr) so that it won't have to be printed out (trying to make us greener)

Any help would be appreciated

Mike G

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

SQL Split function

by p.j.hutchison In reply to Trying to seperate info f ...

You need is a split function to split the field into different elements:

http://www.devx.com/tips/Tip/20009

Collapse -

That's pretty hideous..

by Tony Hopkinson In reply to Trying to seperate info f ...

the SQL Functions CharIndex, Left, Right, and Substring, and possibly DalaLength will come in handy.

e.g.

Select Left([misc], CharIndex('-',[misc]) - 1) as EventDivision From entries

etc

Back to Software Forum
3 total posts (Page 1 of 1)  

Software Forums