asked on

URGENT: Need help with parsing TEXT data using PADINDEX...

Hi,

The padindex doesn't seem to be able to find "<var name="SERIALNUMBER"><string>" due to the double quots (") around SERIALNUMBER. How do I esape it so that it works? See sql below:

select substring(t1, 0, patindex('%</string%', t1)) as t11
from
(select
case when patindex('%<var name=''SERIALNUMBER''><string>%', product) > 0 then
substring(product, patindex('%<var name=''SERIALNUMBER''><string>%', product)+33, 99)
when patindex('%<var name="SERIALNUMBER"><string>%', product) > 0 then
substring(product, patindex('%<var name=''SERIALNUMBER''><string>%', product)+33, 99)
end as t1
from temp_001
) as x

I have "product" data that have both "<var name='SERIALNUMBER'><string>" and "<var name=''SERIALNUMBER''><string>"

This is in reference to: https://www.experts-exchange.com/questions/25337139/URGENT-Need-help-on-parsing-TEXT-data.html

I'm using SQL Server 2005.

Many thanks.

HainKurt

can you please post a sample data and what you want to see

ASKER CERTIFIED SOLUTION

Rimvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bobby X

ASKER

Hi HainKurt,

You can find sample data in the original post: This is in reference to: https://www.experts-exchange.com/questions/25337139/URGENT-Need-help-on-parsing-TEXT-data.html

Thanks.

Bobby X

ASKER

Hi Rimvis,

I'll give it a try tomorrow when I return to work tomorrow morning.

Thanks.

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bobby X

ASKER

HainKurt,

In your "declare @product varchar(max) = '<wddxPacket version=''1.0''><header/><data><struct>...", try to replace the 2 single quotes (' ') around SERIALNUMBER (see below) with double quotes (") in your sql server management studio query analyzer so that it looks like this <var name="SERIALNUMBER"><string>34151461646</string></var>:

<var name=' 'SERIALNUMBER' '><string>34151461646</string></var> --try replacing the 2 single quotes (' ') with double quotes (")

Then run it and see if it works. In other words, my original sample data may have double quotes (") instead of 2 single quotes (' ') around SERIALNUMBER.

Many thanks.

Bobby X

ASKER

Ok, I finally found out what the problem was. The solution was to replace the double qoute (") with 4 single quotes ('''') so that it looks like this:

select substring(t1, 0, patindex('%</string%', t1)) as t11
from
(select
case when patindex('%<var name=''SERIALNUMBER''><string>%', product) > 0 then
substring(product, patindex('%<var name=''SERIALNUMBER''><string>%', product)+33, 99)
when patindex('%<var name=''''SERIALNUMBER''''><string>%', product) > 0 then
substring(product, patindex('%<var name=''''SERIALNUMBER''''><string>%', product)+55, 99)
end as t1
from temp_001
) as x

Many thanks to all of you.