SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Split comma delimitted string with T-SQL?

    I am trying to write a SQL Server stored procedure that splits a comma delimitted string into multiple arrays.
    Eg.
    From:
    str = "12, 1, 5"
    to:
    arr(0) = 12
    arr(1)=1
    arr(2)=5


    Does anyone know if T-SQL has a function similar to vb's "split"?

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't believe arrays are supported in sql so there's no equivilant of VB's Split function. However, these articles might be of help with dealing with comma delimited strings.
    Last edited by shane; Jul 15, 2002 at 09:31.

  3. #3
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    He is talking about the Transact SQL programming language that comes with SQL Server and Sybase. It is above and beyond what is the SQL Standard and used to create stored procedures on those systems.

    I know it supports it, I just can't recall how at the moment since it has been a few years since I worked with T-SQL.
    Wayne Luke
    ------------


  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was referring to T-SQL Wayne but from my knowledge there isn't any equivalent of the Split function or support for arrays in T-SQL.

    I make no claims to being a T-SQL expert though so I could well be wrong.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You'd probably have to do something like this (C developers know and hate this ):
    Code:
    CREATE TABLE #destination_table(
      number INT
    )
    
    DECLARE @position INT
    DECLARE @source_string VARCHAR( 1000 )
    
    SET @source_string = "1,2,3,4,5,6"
    
    SET @position = CHARINDEX( ",", @source_string )
    
    WHILE @position <> 0
    BEGIN
    
      -- You'll probably have to CAST the LEFT since number is an INT, 
      -- or you can change number in #destination_table to be a varchar
      INSERT INTO #destination_table VALUES( LEFT( @source_string, @position ) )
    
      -- I can't recall if STUFF actually modifies @source_string, 
      -- so the assignment may be unnecessary
      SET @source_string = STUFF( @source_string, 0, @position, NULL )
    
    END


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •