SQL Query Substring Query

I have a database column in a SQL Server 2008 database that contains VARCHAR data composed of four elements separated by an underscore; for example:

01_1234_ABC_TESTFOO

02_2234_YES_FOO

03_77653234_NO_BAR

04_10922234_BLUE_TESTBAR

05_8372_SKY_FOOBAR

I need a query to return the first three elements for each row as separate columns. I have managed to separate out the first two elements with the following code:

SELECT DISTINCT SUBSTRING(BarType, 1, CHARINDEX('_', BarType) - 1) as element1,
            SUBSTRING(BarType,CHARINDEX('_',BarType)+1, 
                CHARINDEX('_', SUBSTRING(BarType,CHARINDEX('_',BarType)+1,LEN(BarType)))-1) as element2
FROM dbo.TestDataFoo 

This returns:

    element1   element2 
    01         1234     
    02         2234  
    03         77653234  
    04         10922234  
    05         8372

Could someone help me to get the third element of the data please? I'd also be interested to learn of any alternate methods for dealing with this.

See below for code to generate test data.

CREATE TABLE TestDataFoo (
id int PRIMARY KEY IDENTITY,
DateFoo datetime NOT NULL,
BarType varchar(50) NOT NULL)

INSERT INTO TestDataFoo (DateFoo, BarType)
VALUES(GetDate(), '01_1234_ABC_TESTFOO')
INSERT INTO TestDataFoo (DateFoo, BarType)
VALUES(GetDate(), '02_2234_YES_FOO')
INSERT INTO TestDataFoo (DateFoo, BarType)
VALUES(GetDate(), '03_77653234_NO_BAR')
INSERT INTO TestDat开发者_Python百科aFoo (DateFoo, BarType)
VALUES(GetDate(), '04_10922234_BLUE_TESTBAR')
INSERT INTO TestDataFoo (DateFoo, BarType)
VALUES(GetDate(), '05_8372_SKY_FOOBAR')

Thanks

Nimi

Edit: Ideally, I'd like to achieve this without using a function but any solutions welcome!

;with cte as
(
  select cast('<r><i>'+replace(BarType,'_','</i><i>')+'</i></r>' as xml) as xmlcol
  from TestDataFoo
)
select 
  r.value('i[1]', 'varchar(50)') as element1,
  r.value('i[2]', 'varchar(50)') as element2,
  r.value('i[3]', 'varchar(50)') as element3
from cte
  cross apply xmlcol.nodes('r') r(r)  

Here's a handy function that I think would do the trick.

create function [dbo].[fn_split]
    (
    @String nvarchar (4000)
    ,@Delimiter nvarchar (10)= ','
    )
returns @ValueTable table ([Value] nvarchar(4000))
as

/******************************************************************************
**      name: fn_split
**      desc: splits a delimited list into a table
**              select * from dbo.fn_split('01_1234_ABC_TESTFOO', '_')
*******************************************************************************/
begin

    declare @NextString nvarchar(4000)
    declare @Pos int
    declare @NextPos int
    declare @CommaCheck nvarchar(1)

    --Initialize
    set @NextString = ''
    set @CommaCheck = right(@String,1) 

    --Check for trailing Comma, if not exists, INSERT
    --if (@CommaCheck <> @Delimiter )
    set @String = @String + @Delimiter

    --Get position of first Comma
    set @Pos = charindex(@Delimiter,@String)
    set @NextPos = 1

    --Loop while there is still a comma in the String of levels
    while (@pos <>  0)  
    begin
        set @NextString = substring(@String,1,@Pos - 1)

        insert into @ValueTable ( [Value]) Values (@NextString)

        set @String = substring(@String,@pos +1,len(@String))

        set @NextPos = @Pos
        set @pos  = charindex(@Delimiter,@String)
    end

    return


end

GO

Just use the old PARSENAME trick:

SQL

SELECT 
    PARSENAME(REPLACE(MEGASTRING,'_','.'),4) AS COL1,
    PARSENAME(REPLACE(MEGASTRING,'_','.'),3) AS COL2,
    PARSENAME(REPLACE(MEGASTRING,'_','.'),2) AS COL3,
    PARSENAME(REPLACE(MEGASTRING,'_','.'),1) AS COL4
FROM (
    SELECT '01_1234_ABC_TESTFOO' AS MEGASTRING
    UNION ALL SELECT '02_2234_YES_FOO' AS MEGASTRING
    UNION ALL SELECT '03_77653234_NO_BAR' AS MEGASTRING
    UNION ALL SELECT '04_10922234_BLUE_TESTBAR' AS MEGASTRING
    UNION ALL SELECT '05_8372_SKY_FOOBAR' AS MEGASTRING
) data

Results

COL1 COL2     COL3  COL4
01   1234     ABC   TESTFOO
02   2234     YES   FOO
03   77653234 NO    BAR
04   10922234 BLUE  TESTBAR
05   8372     SKY   FOOBAR