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 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]')))
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy