July 16, 2012 at 12:29 pm
Comments posted to this topic are about the item Get String between two delimiters
July 17, 2012 at 12:35 pm
Thanks Harsha for your post, was looking for something very similar.
Stephen
July 25, 2012 at 10:27 am
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."
July 25, 2012 at 10:48 am
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-2 varchar(max)
declare @1 varchar(max)
set @s-2= ([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]')))
August 6, 2012 at 7:24 am
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.
August 16, 2012 at 2:52 pm
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.
October 4, 2012 at 3:21 pm
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/
February 26, 2013 at 8:20 pm
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
May 10, 2016 at 9:23 am
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