Finding substring between starting and ending xml-tag in DB2

building semantic layer
1

Hello Guys,

I actually need to get the values between <Att_Desc> and </Att_Desc> in DB2, we are using DB2 9 version.

I tried using substring and locate function with no success.

select
substr(columnstring, LOCATE(’<Att_Desc>’, columnstring)+8, ? )
from table

In the above statement I was able to find the location from where i have to start, but I don’t know exact substring length which I need. substring length is varying between tags.

Any helps will be appreciated !

Thanks !

justdesi (BOB member since 2006-06-28)

2

I have a string that contains, amongst other things, the values “X1” and “X2”. I need to find the text value between these two, which could start and end at any position in the overall string. This is VB code, so you’ll have to find the db2 equivalent, but you get the idea.

value=mid$(string,instr(avals(0),"X1")+2,instr(string,"X2")-instr(string,"X1")-2)

get string, start at position X1+2 and extract n characters, where n is (position X2 - position X1 - 2)

Debbie

Debbie :uk: (BOB member since 2005-03-01)

3

For me, the below query worked !!!

SELECT SUBSTR(, LOCATE(’’, )+length(’’), LOCATE(’’, )-(LOCATE(’’, )+length(’’))) from <your_table> where =‘XXXXXXXXXXX’ AND LOCATE(’’, ) > 0

the requirement was to find out 09140XXXX from an xml stored as varchar

Cheers
Viswam

baijuviswam (BOB member since 2012-06-26)