使用 CharIndex、Len 和 Substring 从列中定位某些信息 [英] Using CharIndex, Len and Substring to locate certain information from a column

问题描述

这是我正在尝试做的事情，但失败了:

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

我正在尝试从长度超过 12000 个字符的列中检索地址.幸运的是，我可以通过 XML 标签找到地址 line1:

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

我也尝试在那里压缩长度函数来计算我的子字符串函数的长度结束，但这只是给了我一个错误(可能是因为我是 SQL 新手，仍然在学习和使用它是错误的).

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!

谢谢！

推荐答案

试试这个:

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

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

这篇关于使用 CharIndex、Len 和 Substring 从列中定位某些信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！