SQL Query Substring Query

Question

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 TestDataFoo (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!

Mikael Eriksson · Accepted Answer · 2011-03-18 23:30:51Z

1

;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)

answered Mar 18, 2011 at 23:30

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nimizen Over a year ago

Thanks Mikael, I've not used cross apply before but this works perfectly and without using a function; I'll have to read up on this.

kevin · Accepted Answer · 2011-03-18 23:20:18Z

1

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

answered Mar 18, 2011 at 23:20

kevin

4674 silver badges21 bronze badges

2 Comments

nimizen Over a year ago

Thanks for your answer kevin; I ought to have specified that I was looking to achieve this without using functions but will give this a go.

kevin Over a year ago

well in about 10 seconds you could pull the relevant sections out so its not a function ;)

beach · Accepted Answer · 2011-03-19 02:03:57Z

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

Collectives™ on Stack Overflow

SQL Query Substring Query

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related