Using CharIndex, Len and Substring to locate certa

Question

Here's what I am trying to do, but failing miserably:

I am trying to retrieve address from a column that is 12000+ characters long. Lucky for me, I can locate the address line1 through XML tag:

<PermanentAddress> <AddressLine><![CDATA[1234 1st street]]></AddressLine> <City>

Here's what I have done so far:

select 
substring(PC.css_record, CHARINDEX('<AddressLine>', PC.css_record)+ 21, CHARINDEX('</AddressLine>', PC.css_record))
from 
table1

I tried squeezing length function in there too to calculate the length end of my substring function, but that just gave me an error (may be because I am new to SQL and still learning and used it wrong).

Also, there are multiple tags, but I am only concerned with the first one.

It would be very awesome if someone could help me out!

Thanks!

Answer 1

You found the beginning: CHARINDEX('<AddressLine>', PC.css_record) + 21
and the end: CHARINDEX('</AddressLine>', PC.css_record) - 3,
now you just need to compute the length by subtracting the beginning from the end: CHARINDEX('</AddressLine>', PC.css_record) - CHARINDEX('<AddressLine>', PC.css_record) - 24

select 
    substring(PC.css_record,
              CHARINDEX('<AddressLine>', PC.css_record) + 21,
              CHARINDEX('</AddressLine>', PC.css_record)
                  - CHARINDEX('<AddressLine>', PC.css_record) - 24)
from 
PC

Answer 2

Try this :

declare @xml xml = 
'<PermanentAddress> 
<AddressLine><![CDATA[1234 1st street]]>
</AddressLine> 
<City>test</City>
</PermanentAddress>'  

select @xml.value('(/PermanentAddress/AddressLine)[1]', 'nvarchar(100)')