如何將不同的字元串值轉換為各自的 ‘YYYY’ int 格式？

我將值插入tablefromxml為：

DECLARE @XMLRESULT XML

DECLARE @tbl_XMLResult TABLE
(
   RowID INT IDENTITY(1,1),
   Status VARCHAR(50),
   Address VARCHAR(250),
   ListPrice INT,
   SoldPrice INT,
   YearBuilt INT   
)

EXEC usp_GetReportResult @query = @query, @ReportName = @ReportName, @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult
(
   Status,
   Address,
   ListPrice,
   SoldPrice,
   YearBuilt
)
SELECT
   L.value('(ListingStatus/text())[1]','VARCHAR(50)') AS Status,
   L.value('(PMCReportData/Listing_StreetAddress/text())[1]','VARCHAR(250)') AS Address,
   L.value('(ListPrice/text())[1]','INT') AS ListPrice,
   L.value('(ClosePrice/text())[1]','INT')AS SoldPrice,
   L.value('(YearBuilt/text())[1]','INT')AS YearBuilt
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing')AS Result(L)

但如上列所示，YearBuilt數據類型是int我們儲存的只是year建構了哪個屬性。並相應地完成後續calculations工作。但有時值YearBuilt來自xml於format

• “新的”
• “0-5 歲”
• “70+”

但是當我們得到上述類型的結果YearBuilt時。需要從上面的結果中獲取值，就像 YearBuilt 一樣：xml``conversion error

• “新” - 在這裡我們要考慮當年並將結果儲存為 2016 年。
• “0-5 歲” - 0-5 歲。這裡考慮上限，即 5。因此將結果儲存為 2011 as(2016 - 5 = 2011)
• “70+” - 將 70 視為 => 2016 - 70 年 = 1946

我們如何做到這一點以將適當的結果儲存到@tbl_xmlResult

從 XML 中獲取作為字元串的值，並在 case 語句中進行一些字元串操作。

簡化 XML 的範例：

declare @X xml = '
<YearBuilt>2017</YearBuilt>
<YearBuilt>New</YearBuilt>
<YearBuilt>0-5 Yrs</YearBuilt>
<YearBuilt>70+</YearBuilt>
';

select case
        when T.Value = 'New' then year(getdate())
        when T.Value like '%Yrs' then year(getdate()) - cast(replace(stuff(T.Value, 1, charindex('-', T.Value), ''), 'Yrs', '') as int)
        when T.Value like '%+' then cast(left(T.Value, charindex('+', T.Value) - 1) as int)
        else cast(T.Value as int)
      end
from @X.nodes('/YearBuilt') as Y(X)
 cross apply (
             select Y.X.value('text()[1]', 'varchar(10)')
             ) as T(Value);

引用自：https://dba.stackexchange.com/questions/131827