表函数解析出数组数据
问题描述:
我有一些麻烦,试图分裂出来,我已经存储在SQL表字段的“SQL Server 2014”数组的字符串。表函数解析出数组数据
我的数据看起来像
array(
"status"=>array("pre"=>"2","aft"=>1)
"tier_ppl"=>array("pre"=>5.00,"aft"=>5.00)
"tier_commission"=>array("pre"=>5.00,"aft"=>500.00)
"tier_commission_datetime"=>array("pre"=>"2017-02-10 12:30:59","aft"=>"2017-02-14 08:54:32")
"affiliate_commission"=>array("pre"=>4.00,"aft"=>4.00)
"affiliate_commission_datetime"=>array("pre"=>"2017-02-10 12:30:59","aft"=>"2017-02-10 12:30:59")
)
,我需要让我出去放像: -
arrayname pre aft
status 5.00 5.00
tier_ppl 5.00 500.00
tier_commission 5.00 500.00
tier_commission_datetime 2017-02-10 12:30:59 2017-02-14 08:54:32
affiliate_commission 4.00 4.00
affiliate_commission_datetime 2017-02-10 12:30:59 2017-02-10 12:30:59
每当我有将会有一个数组像这样的记录,虽然有时预或者尾部将是空白的。我已经尝试了拆分函数路线,但我无法使我的数据看起来像我想要的样子,有没有人做过这样的事情之前,可以帮助请。
感谢
答
这是非常,非常,非常不是最好的方式做到这一点。复杂的字符串操作是你可以尝试在SQL Server中做的最糟糕的事情之一。在你的整个项目用你的问题中的愚蠢的设计选择来存储之前,将你的开发团队整理出来。
这一边,因为我在工作无聊,我决定向你展示尝试只是多么愚蠢做,这是在SQL。下面的示例适用于您的示例数据,但在主机pre
和aft
值上可能会失败。例如,如果您的数据中有任何charindex
或patindex
匹配模式。
利用Jeff Moden's string splitting function,你可以做到以下几点:
declare @a nvarchar(1000) =
'array(
"status"=>array("pre"=>"2","aft"=>1)
"tier_ppl"=>array("pre"=>5.00,"aft"=>5.00)
"tier_commission"=>array("pre"=>5.00,"aft"=>500.00)
"tier_commission_datetime"=>array("pre"=>"2017-02-10 12:30:59","aft"=>"2017-02-14 08:54:32")
"affiliate_commission"=>array("pre"=>4.00,"aft"=>4.00)
"affiliate_commission_datetime"=>array("pre"=>"2017-02-10 12:30:59","aft"=>"2017-02-10 12:30:59")
)';
select replace(left(s.Item,charindex('"=',s.Item,1)),'"','') as arrayname
,replace(replace(substring(s.Item,charindex('"pre"=>',s.Item,1),patindex('%,"%',s.Item) - charindex('"pre"=>',s.Item,1)),'"pre"=>',''),'"','') as Pre
,reverse(replace(replace(left(reverse(s.Item),charindex('>=',reverse(s.Item),1)-1),')',''),'"','')) as Aft
,s.Item
from dbo.DelimitedSplit8K(@a,char(10)) s
where left(Item,1) = '"';
将输出:
+-------------------------------+---------------------+----------------------+----------------------------------------------------------------------------------------------------+
| arrayname | Pre | Aft | Item |
+-------------------------------+---------------------+----------------------+----------------------------------------------------------------------------------------------------+
| status | 2 | 1 | "status"=>array("pre"=>"2","aft"=>1) |
| tier_ppl | 5.00 | 5.00 | "tier_ppl"=>array("pre"=>5.00,"aft"=>5.00) |
| tier_commission | 5.00 | 500.00 | "tier_commission"=>array("pre"=>5.00,"aft"=>500.00) |
| tier_commission_datetime | 2017-02-10 12:30:59 | 2017-02-14 08:54:32 | "tier_commission_datetime"=>array("pre"=>"2017-02-10 12:30:59","aft"=>"2017-02-14 08:54:32") |
| affiliate_commission | 4.00 | 4.00 | "affiliate_commission"=>array("pre"=>4.00,"aft"=>4.00) |
| affiliate_commission_datetime | 2017-02-10 12:30:59 | 2017-02-10 12:30:59 | "affiliate_commission_datetime"=>array("pre"=>"2017-02-10 12:30:59","aft"=>"2017-02-10 12:30:59") |
+-------------------------------+---------------------+----------------------+----------------------------------------------------------------------------------------------------+
+0
嗨iamdave,虽然这是一个有用的事情,给我的东西玩我已经说得很清楚,我们的开发团队,这是不是一件应该在后台完成,我完全同意你来自哪里。非常感谢你对此的帮助,我们非常感谢你,并且学到了一些新东西。 – PJD
为什么你想以这种方式首先养活SQL Server数据? – iamdave
不幸的是,阵列数据已经以这种方式存储在数据库/表中,并且由我们的海外Web开发团队完成,我刚才被问到是否有一种方法可以用我描述的方式解析它。 – PJD
我强烈建议你告诉他们这不是一个适合于SQL Server的任务,而且他们应该真的改变他们的数据导入方法。你也应该明智地解释你想要这些数据去哪里?假设每个数组都是不同的表,给定'for'和'aft'值集内的不同数据类型?根据表和列,您希望得到的输出是什么?这也有助于了解空白的“前”或“后”值的外观。 – iamdave