Accessing substrings

kordetk · #1 (**permalink**) 07-20-07, 06:11

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,
tk

r937 · #2 (**permalink**) 07-20-07, 06:33

Code:

select 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

RedNeckGeek · #3 (**permalink**) 07-20-07, 07:23

Do I win a prize for gratuitous use of string functions?

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

Code:

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,len(@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.

r937 · #4 (**permalink**) 07-20-07, 07:36

no fair!! i was trying to restrict myself to the language of this particular forum

RedNeckGeek · #5 (**permalink**) 07-20-07, 08:02

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.

aschk · #6 (**permalink**) 07-26-07, 06:07

varchar2 screams Oracle at me.... should we be looking for Oracle solutions?

aschk · #7 (**permalink**) 07-26-07, 06:10

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?

r937 · #8 (**permalink**) 07-26-07, 06:27

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

Internet Services

Web Development

Digital Marketing

Consumer Tech