SQL Server Split function

is there a Sql split function

I have data in a column that is pipe delimited, example
Region|1339794638   |0

I need to get each value via sql query
LVL 1
countrymeisterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could use the ParmsToList function:
http://www.experts-exchange.com/A_1536.html
but that will give 1 part per row ....
still, you could with a group by reassemble them into 1 row, if needed.
hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OnALearningCurveCommented:
Hi  countrymeister,

Try this example:

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx

Hope this helps,

Mark.
0
ambidextrousCommented:
In the past I have used a function do these types of splits.  Here is an example of parsing out a comma seperated value.  It's probably not the most efficient, but get things done in a data processing scenario.


--Function Call
SELECT
  dbo.fn_ParseComma(ColumnName,0,1) -- Start at beginning (0) and go until 1st Comma
 ,dbo.fn_ParseComma(ColumnName,1,2) -- Start after 1st Comma until 2nd Comma
 ,dbo.fn_ParseComma(ColumnName,2,99) --After 2nd Comma Until End of Row
FROM tblName

Open in new window


Function:
CREATE FUNCTION [dbo].[fn_ParseComma] 
( 
    @str nVarChar(Max), 
    @startcomma Int,
    @endcomma Int
) 
RETURNS nVarChar(max) 
AS 
BEGIN 
    
    Declare @i as Int,
            @j as Int,
            @startpos Int,
            @endpos Int,  
            @result nVarChar(max)

    SET @i = 1
    SET @j = 0

    While @i <= Len(@str)
      BEGIN

        --If Start Comma is "first character"
        if @startcomma = 0 and @i = 1
        BEGIN
          SET @startpos = 1
        END

        --If End Comma is "last character"
        if @endcomma = 99 and @i = len(@str)
        BEGIN
          SET @endpos = @i+1 - @startpos
        END

        If SubString(@str,@i,1) = ','  --Replace comma with other character if necessary.
        BEGIN
          SET @j = @j + 1

          --If we Match Ending Comma Position
          if @j = @endcomma
          BEGIN
            SET @endpos = @i - @startpos
            SET @i = Len(@Str)
          END

          --If we Match Starting Comma Position
          If @j = @startcomma
          BEGIN
            SET @startpos = @i+1
          END

        END

      SET @i = @i + 1
    END

   
    --SET @Result = (SELECT '(' + Cast(@startpos as varchar) + ',' + Cast(@EndPos as varchar) + ')' + SubString(@str,@startpos,@endpos))
    SET @Result = (SELECT SubString(@str,@startpos,@endpos))
    RETURN @Result 
END 

Open in new window

0
Louis01Commented:
ALTER FUNCTION [dbo].[fn_StringToTable] (@string VARCHAR(MAX), @delimiter CHAR(1))
RETURNS @output TABLE(data varchar(256))
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END
    RETURN
END
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.