I Have a LinkedIn Link in my database Column here i have to check with my linkedIn ID where the linked in link is inserted two or more times. ex:https://www.linkedin.com/profile/view?id=246925795&authType=name&authToken=sygl&trk=prof-sb-browse_map-name
我在我的数据库中有一个LinkedIn链接这里我必须检查我的linkedIn ID,其中链接的链接被插入两次或更多次。例如:HTTPS://www.linkedin.com/profile/view ID = 246925795&的authType =名称和的authToken = sygl&TRK =教授-SB-browse_map名
i have to check with id in database if id repeats two or more times it must be deleted and only one must be left. id=246925795
我必须在数据库中检查id,如果id重复两次或多次,则必须删除它,并且只剩下一个。 ID = 246925795
Please Someone Help me
请有人帮帮我
2 个解决方案
#1
1
WITH Ids AS (
SELECT Id,
SUBSTRING(LinkedInLink, IdStart, IdEnd - IdStart) AS LinkedInId
FROM table
CROSS APPLY (
SELECT CHARINDEX('id=', LinkedInLink) AS IdStart
) AS CA1
CROSS APPLY (
SELECT CHARINDEX('&', LinkedInLink, IdStart) AS IdEnd
) AS CA2
)
,Duplicates AS (
SELECT LinkedInId
,MAX(id) AS LastDuplicateId
FROM Ids
GROUP BY LinkedInId
HAVING COUNT(*) > 1
)
DELETE FROM Table
WHERE Id IN (SELECT LastDuplicateId FROM Duplicates)
#2
0
It will be best if you use Regular Expression to do such search opertaion. You can create a Utility class that will perform search + replace in C# or VB.Net and then import that class(DLL) in the SQl Server. In your SQL query you can use it as a function.
最好使用正则表达式来执行此类搜索操作。您可以创建一个Utility类,它将在C#或VB.Net中执行search + replace,然后在SQl Server中导入该类(DLL)。在SQL查询中,您可以将其用作函数。
Refer : Link
参考:链接
#1
1
WITH Ids AS (
SELECT Id,
SUBSTRING(LinkedInLink, IdStart, IdEnd - IdStart) AS LinkedInId
FROM table
CROSS APPLY (
SELECT CHARINDEX('id=', LinkedInLink) AS IdStart
) AS CA1
CROSS APPLY (
SELECT CHARINDEX('&', LinkedInLink, IdStart) AS IdEnd
) AS CA2
)
,Duplicates AS (
SELECT LinkedInId
,MAX(id) AS LastDuplicateId
FROM Ids
GROUP BY LinkedInId
HAVING COUNT(*) > 1
)
DELETE FROM Table
WHERE Id IN (SELECT LastDuplicateId FROM Duplicates)
#2
0
It will be best if you use Regular Expression to do such search opertaion. You can create a Utility class that will perform search + replace in C# or VB.Net and then import that class(DLL) in the SQl Server. In your SQL query you can use it as a function.
最好使用正则表达式来执行此类搜索操作。您可以创建一个Utility类,它将在C#或VB.Net中执行search + replace,然后在SQl Server中导入该类(DLL)。在SQL查询中,您可以将其用作函数。
Refer : Link
参考:链接