Get String between two delimiters

  • Comments posted to this topic are about the item Get String between two delimiters

  • Thanks Harsha for your post, was looking for something very similar.

    Stephen

  • Care to comment as to why it returns this error in SQL 80 AND 90?

    DECLARE @sampleString VARCHAR(1000) = '#This is a sample string# between two delimiters'

    "Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable."

  • Interesting idea, I hadn't thought about reversing the string when doing this before, thanks for posting it. A couple of comments:

    1. The declaration for @samplestring is included twice in your example.

    2. This will only work as intended if the delimiters between which you're searching aren't duplicated (e.g. If the sample string was '###This is a sample string####...' the value returned would be "##This is a sample string###".

    3. I find that using charindex and determining the length of the delimiters rather than including hard coded numbers can save more time when parsing strings this way. Example below:

    declare @S varchar(max)

    declare @1 varchar(max)

    set @S= ([Select Statement goes here])

    set @1 = (select substring(@s,charindex('[1st delimiter]',@s)+len('[1st delimiter]'),charindex('[2nd delimiter]',@s)-charindex('[1st delimiter]',@s)-len('[1st delimiter]')))

  • Hello,

    current block is:

    DECLARE @sampleString VARCHAR(1000) = '#This is a sample string# between two delimiters'

    DECLARE @sampleString VARCHAR(1000) = '#This is a sample string# between two delimiters'

    SELECT Substring(@sampleString, ( Patindex('%#%', @sampleString) + 1 ), ( ( Len(@sampleString) - ( Patindex('%#%', Reverse(@sampleString)) ) + 1 ) - ( Patindex('%#%', @sampleString) + 1 ) ))

    Need to remove "DECLARE @sampleString VARCHAR(1000) = '#This is a sample string# between two delimiters'" as it is declared two times so it gives error...

    Changed block is:

    DECLARE @sampleString VARCHAR(1000) = '#This is a sample string# between two delimiters'

    SELECT Substring(@sampleString, ( Patindex('%#%', @sampleString) + 1 ), ( ( Len(@sampleString) - ( Patindex('%#%', Reverse(@sampleString)) ) + 1 ) - ( Patindex('%#%', @sampleString) + 1 ) ))

    Regards,

    Prashant R.

  • Thanks Prashant!

    After 3 hours of thrashing internet searches, I finally thought "hey, I have an account on SSC! 10 minutes after logging in I used your model and got just what I needed.

  • This is something very similar to a challenge I ran into a few months back. I had a single field with multiple CSV values I wanted to extract various values within that single value at different times. Note: this isn't completely mine, just compiled it from multiple sources.

    First the function, then it's use...

    Function "fn_MyFindPosition"

    --**************************************************************

    --* Name: fn_MyFindPosition @TargetStr, @SearchedStr, @Occurrence

    --* Description: Find the Nth Occurrence of a Character in a String

    --* Parameter Info:

    --*@TargetStr - String value to search within

    --*@SearchedStr - Value of Nth Occurrence of the Character/String

    --*@Occurrence - Number (N) of Nth Occurrence

    --**************************************************************

    ALTER FUNCTION [dbo].[fn_MyFindPosition]

    (

    @TargetStr VARCHAR(8000),

    @SearchedStr VARCHAR(8000),

    @Occurrence INT

    )

    RETURNS INT

    AS

    BEGIN

    -- Declare local variables

    DECLARE @Position INT, @Counter INT, @Return INT

    SET @Position = CHARINDEX(@TargetStr, @SearchedStr)

    SET @Counter = 1

    IF @Occurrence = 1

    BEGIN

    SET @Return = @Position

    END

    ELSE

    BEGIN

    WHILE (@Counter < @Occurrence)

    BEGIN

    SELECT @Return = CHARINDEX( @TargetStr, @SearchedStr, @Position + 1 )

    SET @Counter = @Counter + 1

    SET @Position = @Return

    END

    END

    RETURN( @Return )

    END

    Here is a snippet to illustrate how I used the above function in my 'SPROC'

    DECLARE

    @MultiValueCSVField VARCHAR(200) = 'userid01,4,Chris,Smith,chris.smith@company.com,True,False|',

    @DelimVal VARCHAR(1) = ','

    SELECT

    [UserName] =

    REPLACE( SUBSTRING( @MultiValueCSVField, dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 2 ) + 1 ,

    ( dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 4 ) - dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 2 ) ) - 1 ), @DelimVal, ' ' ),

    [UserEmail] =

    SUBSTRING( @MultiValueCSVField, dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 4 ) + 1 ,

    ( dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 5 ) - dbo.fn_MyFindPosition( @DelimVal, @MultiValueCSVField, 4 ) ) - 1 )

    The output should look like:

    UserName SurveyTaker Email

    Chris Smith chris.smith@company.com

    Other related links to review:

    http://www.sqlmag.com/article/tsql/string-manipulation-tips-techniques-part-1-136427

    http://blog.sqlauthority.com/2010/08/16/sql-server-finding-the-occurrence-of-character-in-string/

  • Hi Tony

    I've used your function (thanks muchly) and the code (again thanks) but my issue is a little different. I have a field with every URL called in our webpage, with a number of '/' and '?'. There may be one or none of these delimiters so I have used the code below. The issue is that because the number of delimiters vary I keep getting stuck. Any help you can offer would be very much appreciated.

    DECLARE

    @DelimVal VARCHAR(1) ='/'

    SELECT page,

    Level1 =

    REPLACE( SUBSTRING( page, dbo.fn_MyFindPosition( @DelimVal, page, 1 ) + 1 ,

    ( dbo.fn_MyFindPosition( @DelimVal, page, 2 ) - dbo.fn_MyFindPosition( @DelimVal, page, 1 ) ) - 1 ), @DelimVal, ' ' )

    level2 =

    SUBSTRING( page, dbo.fn_MyFindPosition( @DelimVal, page, 2 ) + 1 ,

    ( dbo.fn_MyFindPosition( @DelimVal, page, 3 ) - dbo.fn_MyFindPosition( @DelimVal, page, 2 ) ) - 1 )

    ,level3 =

    SUBSTRING( page, dbo.fn_MyFindPosition( @DelimVal, page, 3 ) + 1 ,

    ( dbo.fn_MyFindPosition( @DelimVal, page, 4 ) - dbo.fn_MyFindPosition( @DelimVal, page, 3 ) ) - 1 )

    ,level4 =

    SUBSTRING( page, dbo.fn_MyFindPosition( @DelimVal, page, 4 ) + 1 ,

    ( dbo.fn_MyFindPosition( @DelimVal, page, 5 ) - dbo.fn_MyFindPosition( @DelimVal, page, 4 ) ) - 1 )

    ,level5 =

    SUBSTRING( page, dbo.fn_MyFindPosition( @DelimVal, page, 5 ) + 1 ,

    ( dbo.fn_MyFindPosition( @DelimVal, page, 6 ) - dbo.fn_MyFindPosition( @DelimVal, page, 5 ) ) - 1 )

    --,level6 =

    -- SUBSTRING( page, dbo.fn_MyFindPosition( @DelimVal, page, 6 ) + 1 ,

    -- ( dbo.fn_MyFindPosition( @DelimVal, page, 7 ) - dbo.fn_MyFindPosition( @DelimVal, page, 6 ) ) - 1 )

    from dbo.FAC_Matrix_Stats

  • Thanks for the script.

Viewing 9 posts - 1 through 8 (of 8 total)

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