Regex in sql

Reegz · 07-21-2006, 05:09 AM

Hi guys,

I am trying to extract certain data from a text column in postgres. I need to retrieve only the data between

Code:

<skuCde>8475394</skuCde>

I need "8475394"

How do i extract this using a sql statement??

Thanks

raskin · 07-21-2006, 06:00 AM

select substring (...,'<regexp here, with parenthesis around the part you want>');

Reegz · 07-21-2006, 08:01 AM

Ok, so i'v got this bit working. The problem now is that if i have multiple occurences of skuCde, the output i get is everything between the first opening tag and the last closing tag.

Code:

SELECT aud_id, substring(aud_xml, '<skuCde>\(.*\)</skuCde>' ) from audit where aud_id = 3413;

I've tried adding

Code:

*?

the non-greedy param but then too, i only the first occurence.

Code:

SELECT aud_id, substring(aud_xml, '<skuCde>\(.*?\)</skuCde>' ) from audit where aud_id = 3413;

What am i missing here??

raskin · 07-21-2006, 08:18 AM

Are you sure you store right data in DB? Maybe you'll have to write stored procedure - or not store XML in DB.

Reegz · 07-21-2006, 08:35 AM

yeah, the data is correct. Im just trying to find a way to do this in a script so that i dont have to write a java class to do it.

thanks for your help, i think i'm almost near a solution

Reegz · 07-21-2006, 08:43 AM

psql db9831 -t -c "SELECT aud_xml from audit where aud_id = 3413;" > output;
xmllint --recover -format output > output2;
cat output2 | grep \<sku\> >> output3

raskin · 07-21-2006, 08:59 AM

Well done. I new you'll have to make something else parse XML. When I said right data, I didn't mean that data is corrupted, I meant that if XML lies in SQL DB it is suspicious - XML is some structure and DB is some structure, and they can be a bit not suitable for each other.