SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Harsha Majety / Get String between two delimiters / Latest Posts

RE: Get String between two delimiters

Marie Magdziarz — Tue, 26 Feb 2013 20:20:22 GMT

Hi TonyI'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

RE: Get String between two delimiters

Tony Bromirski — Thu, 04 Oct 2012 15:21:32 GMT

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...[b]Function "fn_MyFindPosition"[/b][code="sql"]--**************************************************************--* 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 INTASBEGIN -- 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 ENDRETURN( @Return ) END[/code][b]Here is a snippet to illustrate how I used the above function in my 'SPROC'[/b][code="sql"]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 )[/code]The output should look like:[code="plain"]UserName SurveyTaker EmailChris Smith chris.smith@company.com[/code][b]Other related links to review:[/b][url]http://www.sqlmag.com/article/tsql/string-manipulation-tips-techniques-part-1-136427[/url][url]http://blog.sqlauthority.com/2010/08/16/sql-server-finding-the-occurrence-of-character-in-string/[/url]

RE: Get String between two delimiters

zweaver — Thu, 16 Aug 2012 14:52:38 GMT

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.

RE: Get String between two delimiters

prashant_rokad — Mon, 06 Aug 2012 07:24:10 GMT

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.

RE: Get String between two delimiters

C-Note — Wed, 25 Jul 2012 10:48:46 GMT

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:[code="other"]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]')))[/code]

RE: Get String between two delimiters

TechnoPeasant — Wed, 25 Jul 2012 10:27:38 GMT

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."

RE: Get String between two delimiters

Stephen Butland — Tue, 17 Jul 2012 12:35:12 GMT

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

Get String between two delimiters

harsha.majety — Mon, 16 Jul 2012 12:29:23 GMT

Comments posted to this topic are about the item [B]Get String between two delimiters [/B]