主页 / dba / 问题 / 249794
Accepted
learning
Asked: 2019-09-28 06:57:20 +0800 CST

解析 URL 链接

  • 772
 I have a large data set of over 10k+ rows and I'm trying to parse the url link
 that people of clicked on

 here is a table: dbo.email_list

 UserID   Cliked_Linked
 101012   https:// amz/profile_center?qp= 8eb6cbf33cfaf2bf0f51
 052469   htpps:// lago/center=age_gap=email_address=caipaingn4535=English_USA
 046894   https://itune/fr/unsub_email&utm=packing_345=campaign_6458_linkname=ghostrider

所以我尝试了这段代码：

UPDATE email_list set Clicked_Link= REVERSE(SUBSTRING(REVERSE(Cliked_Link),,CHARINDEX('.', REVERSE(ColumnName)) + 1, 999))

不幸的是，这没有用。

目标是让链接在“=”符号所在的位置拆分，并且等号之间的任何内容都在其自己的列中

This is the result I hope to have

UserID  COL_1                              COL_2                  COL_3                COL_4
101012  https:// amz/profile_center?qp    8eb6cbf33cfaf2bf0f51    NaN
052469  htpps:// lago/center              email_addres           caipaingn4535         English_USA
046894  https://itune/fr/unsub_email&utm  packing_345          campaign_6458_linknam   ghostrider
sql-server parse

1 个回答

  1. Best Answer

    如果您使用的是 SQL Server 2016 或更高版本，则可以STRING_SPLIT()结合使用PIVOT. 你必须知道你有多少=迹象。

    SELECT UserID, Cliked_Linked, [1],[2],[3],[4],[5]
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY (SELECT NULL)) as rn
FROM dbo.email_list
CROSS APPLY
STRING_SPLIT(Cliked_Linked,'=')
) AS SourceT
PIVOT
(
MAX(value)
FOR rn IN ([1],[2],[3],[4],[5]) 
) as Pvt;

    示例结果集

    在此处输入图像描述 数据库<>小提琴

    • 3

相关问题