467,122 Members | 1,255 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,122 developers. It's quick & easy.

how to split a row item n insert it in another table

hi
i have split the data using the split command now i want to insert the split data to be inserted into another table. there r many records in the previous table i want each record to be split and inserted into the other table one by one.....



25 123454756741 $GPRMC,093729.000,A,1301.7595,N,08015.2751,E,1.13, 69.84,080210,,*3E,BAUTO 0 3/9/2010 3:07:29 PM

i want to split the underlined data using "," and insert it into another table...
there r many such records i want to split each and every records and insert it to another table
Mar 22 '10 #1
  • viewed: 3017
Share:
6 Replies
Soniad
Hi,

In SQL u can use CHARINDEX to split string.

check the following link :
http://www.databasejournal.com/featu...d-PATINDEX.htm

Regards,

"D"
Mar 22 '10 #2
Hi there,
i have split the underlined text using c# code now what i want is to insert it to another table but there would be several such records.
i want to split each record n insert it to another table.
Mar 22 '10 #3
ck9663
Expert 2GB
Is the text from a table? Or a form?

~~ CK
Mar 22 '10 #4
Hi CK

The text is from a table n not a form.
Mar 23 '10 #5
ck9663
Expert 2GB
Here's a pseudo-code...

1.. Parse your string on your SELECT statement using the comma as delimiter/identifier and the dollar sign as the marker to start the parsing.
2.Insert it using the INSERT INTO TABLE .... SELECT col1, ParsedColumnAlias FROM YourTable.

Happy Coding!!!

~~ CK
Mar 23 '10 #6
nbiswas
100+
Try this:

Solution 1(Using XQuery Approach)
Expand|Select|Wrap|Line Numbers
  1. DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
  2. DECLARE @tblDestination(SplittedRecords varchar(100))
  3.  
  4.  
  5.  
  6. SET @str = '25 123454756741 $GPRMC,093729.000,A,1301.7595,N,08015.2751,E,1.13, 69.84,080210,,*3E,BAUTO 0 3/9/2010 3:07:29 PM'
  7. SET @delimiter =','
  8. SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
  9. INSERT INTO @tblDestination
  10. SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
OUTPUT

Expand|Select|Wrap|Line Numbers
  1. 25 123454756741 $GPRMC
  2. 093729.000
  3. A
  4. 1301.7595
  5. N
  6. 08015.2751
  7. E
  8. 1.13
  9. 69.84
  10. 080210
  11.  
  12. *3E
  13. BAUTO 0 3/9/2010 3:07:29 PM
Hope this helps
Mar 24 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Stu Cazzo | last post: by
2 posts views Thread by middletree | last post: by
5 posts views Thread by Willem | last post: by
1 post views Thread by clayalphonso@yahoo.com | last post: by
1 post views Thread by Ben | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.