Find a specific substring using Transact-SQL

Question

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?

Answer 1

look into the PATINDEX function. It has wildcard matching which should help you..

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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)