sql - 通过SQL语句解析

Question

我有一列提供有关如何解析同一表中另一列的信息。该列具有属性名称和它出现在下一列中的值的字符串长度。这是它的样子:

PropertyNames   PropertyValuesString
SP_PartnerCode:S:0:14:FirstName:S:14:5:LastName:S:19:9: InvestProBase2rogerpatterson
SP_PartnerCode:S:0:14:FirstName:S:14:5:LastName:S:19:7: InvestProBase2AaronSchmidt
SP_PartnerCode:S:0:0:FirstName:S:0:6:LastName:S:6:9:    JosephGaultieri
SP_PartnerCode:S:0:14:FirstName:S:14:4:LastName:S:18:9: InvestProBase2ToddEdmondson
SP_PartnerCode:S:0:14:FirstName:S:14:7:LastName:S:21:4: InvestProBase2MichaelLove

我想按属性名称将其分成一列，如下所示:

SP_PartnerCode  FirstName   LastName
InvestProBase2  roger   patterson
InvestProBase2  Aaron   Schmidt
    Joseph  Gaultieri
InvestProBase1  Kevin   Lemmon
InvestProBase1  John    Switzer
InvestProBase2  bryan   abbott
InvestProBase2  Todd    Edmondson
InvestProBase2  Michael Love

这可能吗？

Answer 1

你真的应该标准化你的数据，这是我想到的第一件事。

此函数针对给定属性解析您的数据:

create function Parse(@property nvarchar(4000), 
    @meta nvarchar(4000), @data nvarchar(4000))
returns nvarchar(4000)
as
begin
    set @meta = N':' + @meta

    declare @iproperty int, @itype int, @ibegin int, @ilength int, @iend int
    set @iproperty = charindex(N':' + @property + N':', @meta)
    if @iproperty = 0 return null

    set @itype = charindex(N':', @meta, @iproperty + 1)
    set @ibegin = charindex(N':', @meta, @itype + 1)
    set @ilength = charindex(N':', @meta, @ibegin + 1)
    set @iend = charindex(N':', @meta, @ilength + 1)

    declare @sbegin nvarchar(5), @slength nvarchar(5)
    set @sbegin = substring(@meta, @ibegin + 1, @ilength - @ibegin - 1)
    set @slength = substring(@meta, @ilength + 1, @iend - @ilength - 1)

    declare @begin int, @length int
    set @begin = convert(int, @sbegin)
    set @length = convert(int, @slength)

    if @length = 0 return null

    return substring(@data, @begin + 1, @length)
end

然后选择您的数据

select 
dbo.Parse('SP_PartnerCode', PropertyNames, PropertyValuesString) as SP_PartnerCode,
dbo.Parse('FirstName', PropertyNames, PropertyValuesString) as FirstName,
dbo.Parse('LastName', PropertyNames, PropertyValuesString) as LastName
from MyTable