Accessing substrings

Hi All,

I have an attribute kfld which is varchar2 type and has value as shown below: e.g. kfld= "abc= 2bv= 1fed= 30thb= 200"

my problem is the position of field "fed" is not fixed in the string, So can I get the value '30' (which follows the substring 'fed=' and precedes any alphabet string like tbh, ignore whitespaces) in the output of a single query for a given record.

Rgrd,
tkselect substring(kfld from position('fed=' in kfld)) from ...

this gets you the substring '30thb= 200'

however, detecting the first alphabet now becomes a little trickier|||Do I win a prize for gratuitous use of string functions?

This is using SQL Server, but I assume other systems have similar functions.

declare @.string as varchar(40)
set @.string='abc= 2bv= 1fed= 30thb= 200'

select ltrim(substring(substring(@.string,charindex('fed', @.string)+4,len(@.string)), 1,patindex('%[a-Z]%',substring(@.string,charindex('fed',@.string)+4,le n(@.string)))-1))

That assumes the number following 'fed' is always followed by a letter. If it's "@." or "$", it won't work. Of course, you need to adapt for your table and column.|||no fair!! i was trying to restrict myself to the language of this particular forum|||I did admit I was cheating. Just trying to get the user going in the right direction. Well, maybe not the RIGHT direction, but a direction none-the-less.|||varchar2 screams Oracle at me... should we be looking for Oracle solutions?|||The string that is contained looks like it should be normalised in some fashion or another. Perhaps to a table of id, key=>values. Difficult to tell thought without some more meaningful data.

What string functions are standard in SQL?|||standard SQL string functions are concatenation (accomplished with double pipes ||), UPPER, LOWER, TRIM, TRANSLATE, CHAR_LENGTH, BIT_LENGTH, OCTET_LENGTH, SUBSTRING, and POSITION