I need to pull a specific substring from a string of the form:
foo=abc;bar=def;baz=ghi
For example, how would I get the value of "bar" from that string?
I need to pull a specific substring from a string of the form:
foo=abc;bar=def;baz=ghi
For example, how would I get the value of "bar" from that string?
look into the PATINDEX function. It has wildcard matching which should help you..
You can use charindex and substring. For example, to search for the value of "baz":
declare @str varchar(128)
set @str = 'foo=abc;bar=def;baz=ghi'
-- Make sure @str starts and ends with a ;
set @str = ';' + @str + ';'
select substring(@str,
charindex(';baz=',@str) + len(';baz='),
charindex('=',@str,charindex(';baz=',@str)) - charindex(';baz=',@str) - 1)
Or for the value of "foo" at the start of the string:
select substring(@str,
charindex(';foo=',@str) + len(';foo='),
charindex('=',@str,charindex(';foo=',@str)) - charindex(';foo=',@str) - 1)
Here's a UDF to accomplish this (more readable version inspired by BlackTigerX's answer):
create function dbo.FindValueInString(
@search varchar(256),
@name varchar(30))
returns varchar(30)
as
begin
declare @name_start int
declare @name_length int
declare @value_start int
declare @value_end int
set @search = ';' + @search
set @name_start = charindex(';' + @name + '=',@search)
if @name_start = 0
return NULL
set @name_length = len(';' + @name + '=')
set @value_start = @name_start + @name_length
set @value_end = charindex(';', @search, @value_start)
return substring(@search, @value_start, @value_end - @value_start)
end
As you can see, this isn't easy in Sql Server :) Better do this in the client language, or normalize your database so the substrings go in their own columns.
you can use this function
alter function FindValue(@txt varchar(200), @find varchar(200))
returns varchar(200)
as
begin
declare
@firstPos int,
@lastPos int
select @firstPos = charindex(@find, @txt), @lastPos = charindex(';', @txt, @firstPos+5)
select @lastPos = len(@txt)+1 where @lastPos = 0
return substring(@txt, @firstPos+len(@find)+1, @lastPos-@firstPos-len(@find)-1)
end
select dbo.FindValue('foo=abc;bar=def;baz=ghi', 'bar')
update: was not using the length of @find
I have a generalized solution that works for this problem:
CREATE FUNCTION [dbo].[fn_StringBetween]
(
@BaseString varchar(max),
@StringDelim1 varchar(max),
@StringDelim2 varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @at1 int
DECLARE @at2 int
DECLARE @rtrn varchar(max)
SET @at1 = CHARINDEX(@StringDelim1, @BaseString)
IF @at1 > 0
BEGIN
SET @rtrn = SUBSTRING(@BaseString, @at1
+ LEN(@StringDelim1), LEN(@BaseString) - @at1)
SET @at2 = CHARINDEX(@StringDelim2, @rtrn)
IF @at2 > 0
SET @rtrn = LEFT(@rtrn, @at2 - 1)
END
RETURN @rtrn
END
so if you run (just wrap your original string to be searched with ';' at beginning and end):
PRINT dbo.fn_StringBetween(';foo=abc;bar=def;baz=ghi;', ';bar=', ';')
you will get 'def' returned.
this is assuming that the string will have the same string format just substitute the column name for the 'foo=abc;bar=def;baz=ghi'
select substring('foo=abc;bar=def;baz=ghi',patindex('%bar=%','foo=abc;bar=def;baz=ghi')+4, len('foo=abc;bar=def;baz=ghi')-patindex('%;baz=%','foo=abc;bar=def;baz=ghi')-4)