Dumping data contained in a string in SQL server 2000 database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Dumping data contained in a string in SQL server 2000 database

  1. #1
    Join Date
    Jul 2006
    Posts
    91

    Dumping data contained in a string in SQL server 2000 database

    hai,

    string user records contains following data.
    userRecords= jhon@xyz.com, 100,G ; nate@abc.com,101,S; Juell@pqr.com,102,S

    So, each record is separated by ; and each field by ,

    And the table in the Sql Server 2000 database has following fields:-
    User_records table(Emailid varchar(40), CRN varchar(40), Role char(10) )

    So, Can any one explain me how to get the data contained in the string in to that table?. Or please provide me a good link

    Thanks in advance.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    First step is to use the String.Split Method.

    Once you have that worked out we can move on to the SQL Server code.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    If you prefer, you may pass the delimited string as-is and parse it on the server: http://msdn.microsoft.com/library/en...atYourself.asp
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  4. #4
    Join Date
    Dec 2005
    Location
    South of Nowhere, Maine
    Posts
    309
    It depends on what you want for performance. Assuming your NOT using SQL 2005, then you will probably get better performance splitting the string in your app, and passing up the values.

    If you are using SQL 2005, then you can write native .NET code at the server level, so you could pass in the entire string, then have your .NET code procedure split and insert the records.

  5. #5
    Join Date
    Jul 2006
    Posts
    91
    Quote Originally Posted by pclement
    First step is to use the String.Split Method.

    Once you have that worked out we can move on to the SQL Server code.
    Yes I sliced the string.
    foreach(string s in split)
    { Console. WriteLine("-{0}-,s) }
    //prints sliced strings in 10 iterations

    Now, the problem is Just for 3 records(and 3 fileds in each record), it is taking 10 iterations to split. But, in real time there will be variable number of records and I will not know how many in advance. So, how should I specify the count.

    But, I still want to proceed withthe next step i.e. SQL server code.

    Thanks,

  6. #6
    Join Date
    Aug 2006
    Posts
    1

    Use provided split function

    You can use this split function to split ";" sperated values and then you can parsename function available in SQL Server to get value at given position or you can split further

    Create Function dbo.udf_split(@list varchar(8000), @delimiter char(1))
    returns @t table (r varchar(8000))
    As

    Begin

    set @list = replace(replace(@list,char(10),''),char(13),'')

    insert @t
    SELECT ltrim(SUBSTRING(@delimiter + @List + @delimiter , w.i + 1, CHARINDEX(@delimiter , @delimiter + @List + @delimiter , w.i + 1) - w.i - 1)) value
    FROM (
    SELECT top 100 percent v0.n + v1.n + v2.n + v3.n i
    FROM (
    SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
    )v0,
    (
    SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
    ) v1,
    (
    SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
    ) v2,
    (
    SELECT 0 n UNION ALL SELECT 4096
    ) v3 order by i
    ) w
    WHERE w.i = CHARINDEX(@delimiter , @delimiter + @List + @delimiter , w.i) AND w.i < LEN(@delimiter + @List)

    return
    End

Similar Threads

  1. Replies: 2
    Last Post: 03-24-2005, 06:49 PM
  2. Replies: 0
    Last Post: 04-29-2002, 08:43 AM
  3. Replies: 0
    Last Post: 04-29-2002, 08:39 AM
  4. Database problems
    By Robert Rieth in forum VB Classic
    Replies: 1
    Last Post: 04-11-2000, 04:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center