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.