Solved

How to extract substring from string using CHARINDEX in SQL Server 2008R2

Posted on 2016-08-02
28
100 Views
Last Modified: 2016-08-03
Hi, I am trying to extract strings from within a string in SQL Server 2008.  I have the logic partially working but I am having trouble removing the end of the string that I don't want.  Please see my example below.  My results should show 4.5x10-6 for AA and 5.1 for BB, 12.25 for CC and 123 for DD.

Thanks for your help.
Alexis


DECLARE @StringTest varchar(100) = 'xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123'
SELECT @StringTest as TheString,
       CHARINDEX('AA=',@StringTest) as StartOfLabelAA,
       case when CHARINDEX('AA=',@StringTest) < 0 then '' else CHARINDEX('AA=',@StringTest)+3 end as StartOfValueAA,
	   case when CHARINDEX('AA=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('AA=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('AA=',@StringTest)) 					
				           ) END as TheValueAA,
       CHARINDEX('BB=',@StringTest) as StartOfLabelBB,
       case when CHARINDEX('BB=',@StringTest) < 0 then '' else CHARINDEX('BB=',@StringTest)+3 end as StartOfValueBB,
	   case when CHARINDEX('BB=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('BB=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('BB=',@StringTest)) 					
				           ) END as TheValueBB,
	   CHARINDEX('BB=',@StringTest) as StartOfLabelBB,
       case when CHARINDEX('CC=',@StringTest) < 0 then '' else CHARINDEX('CC=',@StringTest)+3 end as StartOfValueCC,
	   case when CHARINDEX('CC=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('CC=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('CC=',@StringTest)) 					
				           ) END as TheValueCC,
       CHARINDEX('DD=',@StringTest) as StartOfLabelDD,
       case when CHARINDEX('DD=',@StringTest) < 0 then '' else CHARINDEX('DD=',@StringTest)+3 end as StartOfValueDD,
	   case when CHARINDEX('DD=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('DD=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('DD=',@StringTest)) 					
				           ) END as TheValueDD

Open in new window

0
Comment
Question by:alexisbr
  • 13
  • 10
  • 2
  • +2
28 Comments
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 50 total points
ID: 41739349
The 2nd paramter is the number of characters you want to return, so you must subtract your find character from your start position
DECLARE @StringTest varchar(100) 
set @stringtest= 'xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123'
SELECT 
	   case when CHARINDEX('AA=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('AA=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('AA=',@StringTest)-(CHARINDEX('AA=',@StringTest)+3)) 					
				           ) END as TheValueAA,
	   case when CHARINDEX('BB=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('BB=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('BB=',@StringTest)-(CHARINDEX('BB=',@StringTest)+3)) 					
				           ) END as TheValueBB,
	   case when CHARINDEX('CC=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('CC=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('CC=',@StringTest)-(CHARINDEX('CC=',@StringTest)+3)) 					
				           ) END as TheValueCC,
	   case when CHARINDEX('DD=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('DD=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('DD=',@StringTest)-(CHARINDEX('DD=',@StringTest)+3)) 					
				           ) END as TheValueDD

Open in new window

0
 
LVL 32

Expert Comment

by:ste5an
ID: 41739418
You just need a split function returning the row ID, then it's much easier:

DECLARE @Value VARCHAR(255) = 'xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123';
DECLARE @Splitted TABLE ( ID INT IDENTITY, SplittedValue VARCHAR(255) );

INSERT INTO @Splitted
	SELECT	S.[value] 
	FROM	STRING_SPLIT(@Value, '=') S;

WITH Parsed AS (
	SELECT	S.ID,
			S.SplittedValue,
			LEFT(S.SplittedValue, CASE WHEN CHARINDEX(S.SplittedValue, ' ') > 0 THEN (S.SplittedValue) - 2 ELSE 255 END) AS PrevValue,
			RIGHT(S.SplittedValue, 2) AS CurrTag
	FROM	@Splitted S
	)
	SELECT	L.CurrTag, R.PrevValue
	FROM	Parsed L
		INNER JOIN Parsed R ON R.ID = L.ID + 1;

Open in new window


See also Split strings the right way – or the next best way.
0
 

Author Comment

by:alexisbr
ID: 41739543
Thank you both for your responses.

Randy, using your code, AA and BB do not have the correct values.    AA is missing "-6" at the end and BB has 3 extra characters " CC" after the correct value .   I found the problem was due to a parenthesis in the wrong place. I fixed that by moving one of the parentheses in the middle of each line to the end of each line.  But then the last value, DD, causes an error because there is no space after the "123".  When I add, "xxx" to the end after "123", it works but I need to be able to just use the length of the substring if no space exists in the substring.  Do you know how to do this?  It's probably another nested charindex but I'm trying to figure out the syntax.

Ste5an, I get the error "Invalid object name 'STRING_SPLIT'" running your code.

Thanks,
Alexis

DECLARE @StringTest varchar(100) 
set @stringtest= 'xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123  xxx'
SELECT 
	   case when CHARINDEX('AA=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('AA=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('AA=',@StringTest))-(CHARINDEX('AA=',@StringTest)+3) 					
				           ) END as TheValueAA,
	   case when CHARINDEX('BB=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('BB=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('BB=',@StringTest))-(CHARINDEX('BB=',@StringTest)+3) 					
				           ) END as TheValueBB,
	   case when CHARINDEX('CC=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('CC=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('CC=',@StringTest))-(CHARINDEX('CC=',@StringTest)+3) 					
				           ) END as TheValueCC,
	   case when CHARINDEX('DD=',@StringTest) < 0 then '' 
		    else  substring(@StringTest,
			                CHARINDEX('DD=',@StringTest)+3, 
							CHARINDEX(' ',@StringTest, CHARINDEX('DD=',@StringTest))-(CHARINDEX('DD=',@StringTest)+3) 					
				           ) END  as TheValueDD

Open in new window


Here are the results from your code before I moved the parentheses:
TheValueAA      TheValueBB      TheValueCC      TheValueDD
4.5x10                   5.1 CC       12.25                    123
0
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 50 total points
ID: 41739549
First of all: There is a typo. It must be

DECLARE @Value VARCHAR(255) = 'xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123';
DECLARE @Splitted TABLE ( ID INT IDENTITY, SplittedValue VARCHAR(255) );

INSERT INTO @Splitted
	SELECT	S.[value] 
	FROM	STRING_SPLIT(@Value, '=') S;

WITH Parsed AS (
	SELECT	S.ID,
			S.SplittedValue,
			LEFT(S.SplittedValue, CASE WHEN CHARINDEX(' ', S.SplittedValue) > 0 THEN LEN(S.SplittedValue) - 2 ELSE 255 END) AS PrevValue,
			RIGHT(S.SplittedValue, 2) AS CurrTag
	FROM	@Splitted S
	)
	SELECT	L.CurrTag, R.PrevValue
	FROM	Parsed L
		INNER JOIN Parsed R ON R.ID = L.ID + 1;

Open in new window


Just take a look at the link, as you're on SQL Server 2008, you need to create a split function first.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41739589
I would take a step by step approach in order to simplify the code readability and logic. This will work with any number of labels and of any length, not just 3, if you just add them  in the labels union query. It will also work if some of the label/value pair is missing from the string:
DECLARE 
    @StringTest varchar(100) = 'xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123'

;with string as
( 
    select @StringTest as StringValue 
)
,labels as
(
    select 'AA=' as Lable
    union all
    select 'BB=' as Lable
    union all
    select 'CC=' as Lable
    union all
    select 'DD=' as Lable
)
,step1 as
(
    select
	   StringValue,
	   Lable,
	   len(Lable) as LableLen,
	   charindex(Lable,StringValue) as LablePos
    from 
	   string
	   cross apply labels s
) --select * from step1
,step2 as
(
    select
	   StringValue,
	   Lable,
	   LablePos,
	   LableLen,
	   substring(StringValue,LablePos+LableLen,isnull((select min(LablePos) from step1 where LablePos>s1.LablePos)-(LablePos+LableLen),len(StringValue))) as Value
    from
	   step1 s1
    where
	    LablePos>0
) 
select 
    * 
from 
    step2

Open in new window

0
 

Author Comment

by:alexisbr
ID: 41739590
Thanks, Ste5an, but I don't understand the split function and what the link talks about.  I better stick with the other way, especially since I will be working with a table, and I don't know how to apply your example to the real data I am working with.

Alexis
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41739645
How about my method. Will it work?
0
 

Author Comment

by:alexisbr
ID: 41739658
Hi Zberteoc,
Sorry I got pulled into a meeting after I made that other post.

I tried your example and it works perfectly with the example I gave.  Now I have to figure out how to apply it to my real data, which comes from a table.  I am going to create a function using your example.  Then I can pass my string to it and get a return value.  I am running into another meeting but will get back to this soon.  Thank you again.

Alexis
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41739679
You don't have to create function but that would simplify things Here is the version with table:
drop table #temp
go

select * into #temp from (
select 'x AA=4.5x10-6 BB=5.1 CC=12.25 DD=123' as StringValue
union all
select 'xx AA=4.5x10-6 BB=5.1 DD=124' as StringValue
union all
select 'xxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=555' as StringValue
) q

;with string as
( 
    select distinct 
	   StringValue 
    from
	   #temp
)
,labels as
(
    select 'AA=' as Lable
    union all
    select 'BB=' as Lable
    union all
    select 'CC=' as Lable
    union all
    select 'DD=' as Lable
)
,step1 as
(
    select
	   StringValue,
	   Lable,
	   len(Lable) as LableLen,
	   charindex(Lable,StringValue) as LablePos
    from 
	   string
	   cross apply labels s
) --select * from step1
,step2 as
(
    select
	   StringValue,
	   Lable,
	   LablePos,
	   LableLen,
	   substring(StringValue,LablePos+LableLen,isnull((select min(LablePos) from step1 where StringValue=s1.StringValue and LablePos>s1.LablePos)-(LablePos+LableLen),len(StringValue))) as Value
    from
	   step1 s1
    where
	    LablePos>0
) 
select 
    * 
from 
    step2
order by 
    StringValue,
    LablePos

Open in new window

In the substring subquery I added StringValue=s1.StringValue to match the same strings together and from the original table(#temp in my example) you will need to select distinct strings to make sure the parse is not messed up by same values multiple times. A function will not have to deal with these aspects.

Note: The drop statement will give you error first time you run, however the parse part will still work. Subsequent runs will have no errors, though.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41739769
This would be the function:
CREATE FUNCTION [dbo].[fnParseStringLables]
(
	@str VARCHAR(8000)
)
RETURNS TABLE 
AS
RETURN
    with string as
    ( 
	   select @str as StringValue 
    )
    ,labels as
    (
	   select 'AA=' as Lable
	   union all
	   select 'BB=' as Lable
	   union all
	   select 'CC=' as Lable
	   union all
	   select 'DD=' as Lable
    )
    ,step1 as
    (
	   select
		  StringValue,
		  Lable,
		  len(Lable) as LableLen,
		  charindex(Lable,StringValue) as LablePos
	   from 
		  string
		  cross apply labels s
    ) --select * from step1
    ,step2 as
    (
	   select
		  StringValue,
		  Lable,
		  LablePos,
		  LableLen,
		  substring(StringValue,LablePos+LableLen,isnull((select min(LablePos) from step1 where LablePos>s1.LablePos)-(LablePos+LableLen),len(StringValue))) as Value
	   from
		  step1 s1
	   where
		   LablePos>0
    ) 
    select 
	   * 
    from 
	   step2

Open in new window

And this is how you use it:
drop table #temp
go

select * into #temp from (
select 'x AA=4.5x10-6 BB=5.1 CC=12.25 DD=123' as StringValue
union all
select 'xx AA=4.5x10-6 BB=5.1 DD=124' as StringValue
union all
select 'xxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=555' as StringValue
) q

select 
    t.*,
    prs.*
from
    #temp t
    cross apply [dbo].[fnParseStringLables](StringValue) prs

Open in new window


Let's say you need to add anpother label called: EEE= then the only thing you need to modify is to add it in the function:
...    ,labels as
    (
	   select 'AA=' as Lable
	   union all
	   select 'BB=' as Lable
	   union all
	   select 'CC=' as Lable
	   union all
	   select 'DD=' as Lable

	   -- new lable
	   union all
	   select 'EEE=' as Lable
    )
...

Open in new window

0
 

Author Comment

by:alexisbr
ID: 41739775
Thanks.  I'm using your logic to write a function that returns one substring at a time.  I am passing the searchstring and the substring I want to find so I won't need to use the union query.

Alexis
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41739803
You can't do that unless you know what label is right after the one you are looking for and you would need to pass that too, which you don't know. You have to create the code proof of any surprises.
0
 

Author Comment

by:alexisbr
ID: 41739810
Yes, I see what you mean.  I have it running and the returned value starts with the correct substring but does not stop at the first blank space.  I will have to keep working on this.

Thanks,
Alexis
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41739813
The simplest is to leave the union and then filter with where clause:
select 
    t.*,
    prs.*
from
    #temp t
    cross apply [dbo].[fnParseStringLables](StringValue) prs
where
    prs.Lable='CC='

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Accepted Solution

by:
Zberteoc earned 400 total points
ID: 41739817
Or you can use a second parameter for the label but you will still have to leave the union in place:
USE [zb_dba_maint]
GO
/****** Object:  UserDefinedFunction [dbo].[fnParseStringLables]    Script Date: 8/2/2016 4:26:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnParseStringLables]
(
	@str VARCHAR(8000)
	,@lable varchar(10)
)
RETURNS TABLE 
AS
RETURN
    with string as
    ( 
	   select @str as StringValue 
    )
    ,labels as
    (
	   select 'AA=' as Lable
	   union all
	   select 'BB=' as Lable
	   union all
	   select 'CC=' as Lable
	   union all
	   select 'DD=' as Lable

	   -- new lable
	   union all
	   select 'EEE=' as Lable
    )
    ,step1 as
    (
	   select
		  StringValue,
		  Lable,
		  len(Lable) as LableLen,
		  charindex(Lable,StringValue) as LablePos
	   from 
		  string
		  cross apply labels s
    ) --select * from step1
    ,step2 as
    (
	   select
		  StringValue,
		  Lable,
		  LablePos,
		  LableLen,
		  substring(StringValue,LablePos+LableLen,isnull((select min(LablePos) from step1 where LablePos>s1.LablePos)-(LablePos+LableLen),len(StringValue))) as Value
	   from
		  step1 s1
	   where
		   LablePos>0
    ) 
    select 
	   * 
    from 
	   step2
    where 
	   Lable=isnull(@lable,Lable)

Open in new window

You will use it like this:
-- return speciffic label
select 
    t.*,
    prs.*
from
    #temp t
    cross apply [dbo].[fnParseStringLables](StringValue,'CC=') prs


-- return all label if pass NULL as label
select 
    t.*,
    prs.*
from
    #temp t
    cross apply [dbo].[fnParseStringLables](StringValue,null) prs

Open in new window

0
 

Author Comment

by:alexisbr
ID: 41739842
I found another way to do the function that was more simplistic for me (my SQL skills are not expert level).  Thanks, Zberteoc.  Working through your code helped me figure out another way that I could understand better.  I will try my new function with the real data and see if it works.  If not, I will use the way you said.  Thanks again for your help.  I will post results tomorrow.

create FUNCTION [dbo].[getSubstringFromString]
(
	-- Add the parameters for the function here
	@SearchString  VARCHAR(250),
	@SubstringToFind   VARCHAR(10)
)
RETURNS VARCHAR(250)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @SubstringToReturn VARCHAR(250)
	DECLARE @string1 varchar(250)
	DECLARE @IndexStartSubstring integer
	DECLARE @IndexStartBlank integer
	-- Add the T-SQL statements to compute the return value here
	SET @IndexStartSubstring=CHARINDEX(@SubstringToFind,@SearchString)
	SET @IndexStartBlank= CASE WHEN CHARINDEX(' ',@SearchString,@IndexStartSubstring) <> 0 --blank exists but what if end of line so have to handle it 
	  THEN   CHARINDEX(' ',@SearchString,@IndexStartSubstring)
	  ELSE  len(@SearchString) + 1
     END
	 SET @SubstringToReturn = substring(@SearchString,@IndexStartSubstring+3,@IndexStartBlank-(@IndexStartSubstring+3))

--	-- Return the result of the function
	RETURN @SubstringToReturn

END

Open in new window


select dbo.getSubstringFromString('xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123','DD=')
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41739855
That function is not secure. This will not work:

select dbo.getSubstringFromString('xxxxx AA=4.5x10-6 BB=5.1CC=12.25 DD=123','BB=')

If the string misses the space for any reason will return wrong value. My version doesn't care of spaces or any other character, just the labels. On the other hand if you had mentioned that spaces are there 100% then you don't even need a function and that would have spared me the time. :o)
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41739867
Do you always have four labels and space before them?
0
 

Author Comment

by:alexisbr
ID: 41740013
No, sometimes none of the values are there and sometimes a few of the values are there.  There are always other values before the labels and sometimes there are other values after the labels, if they are there at all.

Alexis
0
 

Author Comment

by:alexisbr
ID: 41740015
Zberteoc,
The spaces are not there all the time.  Sometimes they are there.  This field is  a comments field that the users unfortunately decided to use to store values sometimes.  Now they have asked to be able to report on these values so I'm trying to extract the values if they exist.

Thanks,
Alexis
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41740182
No, sometimes none of the values are there and sometimes a few of the values are there.  There are always other values before the labels and sometimes there are other values after the labels, if they are there at all.

Can you provide some examples? How many maximum labels you want to extract?
0
 

Author Comment

by:alexisbr
ID: 41740526
Hi Sharath,
I gave an example in my original question.  Here it is:
DECLARE @StringTest varchar(100) = 'xxxxx AA=4.5x10-6 BB=5.1 CC=12.25 DD=123'

For now the maximum labels are 4 but, in the future, it could be more so I don't want to tie myself down to a number of labels.  Sometimes all of the labels are there and sometimes none are there and sometimes only 1 or 2 of the labels are there.  

Thanks,
Alexis
0
 

Author Comment

by:alexisbr
ID: 41740574
Hi everyone,
I want to thank you all for your help.  I am still finalizing my code.   I have to run into a meeting now and won't be back to my desk for several hours but I wanted to let you know that I ended up using the little function I wrote (based on my back and forth with Zberteoc yesterday.  I know my function will not handle all situations but I have improved it further and I think it will suffice.  The users put several number values into this text field without approval.  I want to help them but I can't spend any more time on this issue.  I am also displaying the entire comments field for the users so they can go back into the data entry form and put an extra space in if it's not there or make other tweaks so my program will pick up their data.

Once I get a final version, I will post the solution I used but I didn't want anyone to spend more time on this while I think I have a working solution.

Thanks, Zberteoc, for all your help.  Your coding skills are way better than mine and I did try for several hours last night to get your code working with my real data but I just couldn't get it to work.  You helped me work through a solution and, for that, I am very grateful and I will award points accordingly.

Regards,
Alexis
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41740630
You are free to choose your solution based on your needs but honestly I can't understand what part of my code didn't work as I provided you several, not only one, all working solutions. So, at least I would like to see what are you referring to. It is my reputation at stake, here. :o)))
0
 

Author Comment

by:alexisbr
ID: 41741019
I'm not saying your code didn't work.  I'm saying my skills are not good enough to implement your solution into my program in my live database.  I gave a basic example just to get the gist of what I was missing and then I apply the solutions to my programs.   I spent hours last night trying to implement your solution into my code but I just couldn't get it to work.  That's when I decided I had to implement what I could get working.  Your code and your suggestions were instrumental in me understanding how to write a more basic function that I could implement.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41741039
But I gave you that function in 2 versions and clear examples how to use it. I also mentioned that it is flexible and will work with different scenarios you may run into. What part exactly didn't work? Have you read all my posts?
0
 

Author Comment

by:alexisbr
ID: 41741104
Hi Zberteoc,
I read all your posts.  I don't totally understand your code to be able to apply it to my existing stored procedure.  I don't know how else to explain that to you.  I got another function working that should be good enough.  I am the sole programmer in a medium sized company with tons of work to do and I just can't spend any more time trying to use your program, especially since the one I wrote works in all the scenarios that are important to my users.  I hope that I can try this again when I am not so pressed for time with all my other work.  Honestly, I tried so hard to get your code working because I know you spent a lot of time helping me, and I really appreciate that.  Your code is here and I'm sure it will be used by other people trying to do the same thing I was trying to do but who have better SQL skills than I do.

Thank you again for all your help.
Alexis

Here's the final working function that I used: (note this function assumes a space is between the different labels/values in the string)
ALTER FUNCTION [dbo].[getSubstringFromString]
(
	-- Add the parameters for the function here
	@SearchString  VARCHAR(250),
	@SubstringToFind   VARCHAR(10)
)
RETURNS VARCHAR(250)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @SubstringToReturn VARCHAR(250)
	DECLARE @string1 varchar(250)
	DECLARE @IndexStartSubstring integer
	DECLARE @IndexStartBlank integer
	-- Add the T-SQL statements to compute the return value here
	SET @IndexStartSubstring=CHARINDEX(@SubstringToFind,@SearchString)
	SET @IndexStartBlank= CASE WHEN CHARINDEX(' ',@SearchString,@IndexStartSubstring) <> 0 --blank exists but what if end of line so have to handle it 
	  THEN   CHARINDEX(' ',@SearchString,@IndexStartSubstring)
	  ELSE  len(@SearchString) + 1
     END
	 SET @SubstringToReturn = substring(@SearchString,@IndexStartSubstring+len(@SubstringToFind),@IndexStartBlank-(@IndexStartSubstring+len(@SubstringToFind)))

--	-- Return the result of the function
	RETURN @SubstringToReturn

END

Open in new window

0
 

Author Closing Comment

by:alexisbr
ID: 41741111
Thank you very much everyone for your help.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now