Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get String between two delimiters Expand / Collapse
Author
Message
Posted Monday, July 16, 2012 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
Comments posted to this topic are about the item Get String between two delimiters
Post #1330327
Posted Tuesday, July 17, 2012 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:26 PM
Points: 3, Visits: 105
Thanks Harsha for your post, was looking for something very similar.

Stephen
Post #1330985
Posted Wednesday, July 25, 2012 10:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:14 AM
Points: 51, Visits: 71
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."



Post #1335280
Posted Wednesday, July 25, 2012 10:48 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 24, 2014 10:42 AM
Points: 82, Visits: 128
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]')))









Post #1335303
Posted Monday, August 06, 2012 7:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 5:24 AM
Points: 43, Visits: 99
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.
Post #1340593
Posted Thursday, August 16, 2012 2:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 17, 2012 11:01 AM
Points: 1, Visits: 15
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.
Post #1346253
Posted Thursday, October 04, 2012 3:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 3:22 PM
Points: 1, Visits: 9
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/
Post #1368751
Posted Tuesday, February 26, 2013 8:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 6:13 PM
Points: 78, Visits: 30
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
Post #1424307
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse