substring/charindex

  • Hi all,

    I need to split the following string: 'A,B,C'

    In Oracle I'd use instr() function to get the position of commas and pull the values in between.

    Sql Server has charindex() function, but I can't get how you'd specify the position of the 2nd comma?

    Thanks,

  • this would show what ever was between the 1st comma and the second

    declare @string varchar(20)

    set @string='A,B,C'

    Select substring( SUBSTRING(@string,charindex(',',@string)+1,99),0,charindex(',',SUBSTRING(@string,charindex(',',@string)+1,99)))

    EDIT

    also take a look at this post

    http://www.sqlservercentral.com/Forums/Topic1368056-391-1.aspx#bm1368081

    ***The first step is always the hardest *******

  • what's 99 for?

  • And a link to my favorite string splitter. Don't leave home without it...

    http://www.sqlservercentral.com/articles/Tally+Table/72993/


    And then again, I might be wrong ...
    David Webb

  • Thank you David,

    I think your article is great, but I think it's to much for me to go through to be able to split 'A,B,C' without hardcoding positions.

  • 99 reflects the potential length of a string between the 1st comma and the second comma

    Substring syntax

    Substring (expression,startpoint,lenght)

    99 represents thee Length of the expression

    build the query up have a play with substring

    here another example with multiple results

    create table col (col varchar(255))

    insert into col select 'A,B,C' union all

    select 'AA,BBBB,CCC'union all

    select 'AAA,BBBBBBBBBBBBBBBBBBBBBBBBBBBBB,CCC'union all

    select 'AAAAAAA,BBBBBBBB,CCC'

    Select substring( SUBSTRING(col,charindex(',',col)+1,99),0,charindex(',',SUBSTRING(col,charindex(',',col)+1,99)))

    from #t1

    ***The first step is always the hardest *******

  • Thank you for an example.

    I ran it and I think I understand it, but my question still is: can you pick a value between 2 commas without hard-coding the length?

    Could you just find a position of a first and second comma?

  • Once you create the function, it's really not that hard...

    declare @dept varchar(200)

    set @dept = 'A,B,C'

    select item from [DelimitedSplit8K](@dept,',')

    item

    A

    B

    C

    The article is by Jeff Moden (who had probably forgotten more about this stuff than I'll ever know).


    And then again, I might be wrong ...
    David Webb

  • this gives you the charindex of comma 1 and comma 2

    select charindex (',',col) as comma1,charindex(',',SUBSTRING(col,charindex(',',col)+1,99))+charindex (',',col) as comma2

    from #t1

    ***The first step is always the hardest *******

  • try this one

    declare @string varchar(20)

    set @string='e33,B6661,C1'

    select substring(@string,charindex(',',@string)+1,charindex(',',@string,charindex(',',@string)+1)-charindex(',',@string)-1)

  • If you need to split value in multiple rows (eg. in SELECT query), You better to use [DelimitedSplit8K] or some CLR splitter.

    For single value split (let say you pass the comma separated string into stored proc), any LOOP-based splitter will be fine, and actually proper-written LOOP-based splitter will even win over tally-table based DelimitedSplit8K.

    If you know that number of comma separated values is constant and it's just 3, you can even use this:

    DECLARE @STR VARCHAR(1000) = 'AAAAAA,BBBBBBBB,CCCCCCCCCC'

    --Return as three separate values (you can set three separate variables here)

    SELECT PARSENAME(S,3) Val1

    ,PARSENAME(S,2) Val2

    ,PARSENAME(S,1) Val3

    FROM (SELECT REPLACE(@str,',','.')) S(S)

    --Return as table (you can join to it, just wrap it inn CLR or use it as subquery))

    SELECT PARSENAME(S,I) Val

    FROM (SELECT REPLACE(@str,',','.')) S(S)

    CROSS JOIN (VALUES (1),(2),(3)) I(I)

    ORDER BY I DESC

    Please note, the above code assumes that there is no "." in a string, but it can be modified to support it.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Thanks Eugene and everyone else.

    I have plenty of options now. I really like Eugene's example of using PARSENAME().

    I think I am going to use it as it fits my case.

    I did came up with the following as well:

    declare @string varchar(20)

    set @string='XyZ,KtrL,AbC'

    select

    @string,

    SUBSTRING(@string,1,charindex(',',@string)-1) first_value,

    substring(@string,charindex(',',@string)+1,charindex(',',@string,charindex(',',@string)+1)-charindex(',',@string)-1) second_value,

    reverse(substring(REVERSE(@string),1,charindex(',',REVERSE(@string))-1)) third_value

  • The DelimitedSplit8k option has been shown to be among the most efficient methods for splitting strings. Another less-complicated but les efficient option is below. Whatever method you choose, I'd recommend a Table-Valued Function so you can JOIN or CROSS APPLY to the split data.

    CREATE FUNCTION [dbo].[tvfParseDelimitedString]

    (

    @s-2 NVARCHAR(MAX) -- Delimited input string

    ,@Split CHAR(1) -- Delimiter used for the input string

    )

    RETURNS @Table TABLE

    (

    [ID] INT NOT NULL IDENTITY(1,1)

    ,[Value] NVARCHAR(MAX) NULL

    ,PRIMARY KEY ([ID])

    ,UNIQUE ([ID])

    )

    BEGIN

    DECLARE @X XML

    SET @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>')

    INSERT INTO @Table

    SELECT T.c.value('.','NVARCHAR(MAX)') AS [Value]

    FROM @X.nodes('/root/s') T (c)

    RETURN

    END

    GO

    Here are examples of how to call this function and the DelimitedSplit8k function. These functions produce tables much like a view or temp table that you can join against.

    DECLARE @sDelimitedString NVARCHAR(4000)

    SET @sDelimitedString = 'A,B,C'

    SELECT ID, Value FROM dbo.tvfParseDelimitedString(@sDelimitedString,',')

    SELECT ItemNumber, Item FROM dbo.tvfDelimitedSplit8K(@sDelimitedString,',')

    Here's another very simple example where the delimited string is split and used to join with another table:

    DECLARE @sDelimitedString NVARCHAR(4000)

    SET @sDelimitedString = 'A,B,C'

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

    DROP TABLE #TempTable

    CREATE TABLE #TempTable (

    [ID] INT NOT NULL,

    [Name] NVARCHAR(50) NULL,

    [Item] NVARCHAR(50) NULL,

    PRIMARY KEY (ID),

    UNIQUE (ID))

    --test data

    INSERT INTO #TempTable

    SELECT

    1 AS ID

    ,'Apple' AS Name

    ,'A' AS Item

    UNION

    SELECT

    2 AS ID

    ,'Banana' AS Name

    ,'B' AS Item

    UNION

    SELECT

    3 AS ID

    ,'Carrot' AS Name

    ,'C' AS Item

    SELECT

    t.ID

    ,Name

    ,Item

    ,Value

    FROM

    #TempTable AS t

    CROSS APPLY

    dbo.tvfParseDelimitedString(@sDelimitedString,',') AS pds

    WHERE

    t.Item = pds.Value

     

  • This is a great method I have used a number of times. It uses a function and cross apply.

    http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply