Help parsing field

[neolempires2@gmail.com]

hi..i need help for my exam, I retrived data from my sms inbok and
insert to table.The record just like this:
ID1,12x100,56x1 2,90x90..,how to pars the table in to:
ID First Second
ID1 12 100
ID1 56 12
ID1 90 90

Thank for your help...

=============== ====

[markc600@hotmail.com]

Re: Help parsing field

Using a table of numbers

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

CREATE TABLE MyTable(ID VARCHAR(4), Data VARCHAR(100))
INSERT INTO MyTable(ID, Data) VALUES('ID1','1 2x100,56x12,90x 90')

SELECT ID,
PARSENAME(REPLA CE(Data,'x','.' ),2) AS First,
PARSENAME(REPLA CE(Data,'x','.' ),1) AS Second
FROM (
SELECT ID,
SUBSTRING(Data ,
Number,
CHARINDEX(',' ,
Data+',' ,
Number)-Number) AS Data
FROM MyTable
INNER JOIN Numbers ON Number BETWEEN 1 AND LEN(Data)+1
AND SUBSTRING(','+D ata,Number,1) = ',') X
ORDER BY ID

[neolempires2@gmail.com]

Re: Help parsing field

you the best ,thx.
if data inserted to table about 100 data/sec,is the script run well in
this condition, and no data lost or error??

[Ed Murphy]

Re: Help parsing field

neolempires2@gm ail.com wrote:

if data inserted to table about 100 data/sec,is the script run well in
this condition, and no data lost or error??

The best way to find out is to actually set up a test.

I've seen the following method used in production systems; it avoids
forcing the insert process to wait for an immediate response, and also
allows trying again later in case of problems.

* insert the data into an intermediate table with a column indicating
order of insertion
* separate process, launched on a schedule or by a human operator:
- note the most recent record that exists
- process all rows up to and including that one, either deleting
them or marking them as already processed
- any rows inserted mid-process are left for the next launch