提问人：Ricky 提问时间：3/16/2023 最后编辑：marc_sRicky 更新时间：3/16/2023 访问量：74
从具有多个变体的字符串值返回整数，SQL Server 2018
Return Integer from a string value with multiple variations, SQL Server 2018
问：
尝试从字符串值中选择帐户和主体 ID 作为整数，字符串的长度和特殊字符各不相同，标识帐户的值可以是 accounts 或 AccountId - 与主体类似，帐户长度应始终为 6，主体长度应始终为 9。
数据存储为字符串，而不是 Blob。
我正在尝试从字符串中提取id值作为整数，输出应如下所示
答：
2赞
Patrick Hurst
3/16/2023
#1
使用假定的 DML/DDL：
SELECT *, TRY_CAST(CASE WHEN CHARINDEX('PrincipalID=',string) > 0 THEN SUBSTRING(string,CHARINDEX('PrincipalID=',string)+LEN('PrincipalID='),9)
WHEN CHARINDEX('principals/',string) > 0 THEN SUBSTRING(string,CHARINDEX('principals/',string)+LEN('principals/'),9)
END AS INT) AS PrincipalID,
TRY_CAST(CASE WHEN CHARINDEX('AccountID=',string) > 0 THEN SUBSTRING(string,CHARINDEX('AccountID=',string)+LEN('AccountID='),6)
WHEN CHARINDEX('Accounts/',string) > 0 THEN SUBSTRING(string,CHARINDEX('Accounts/',string)+LEN('Accounts/'),6)
END AS INT) AS AccountID
FROM @Strings
在这里，我们使用 和 进行字符串操作（可能更适合另一层）来确定示例中四个字符串的字符串位置，计算它们的结束位置，获取预期的字符数，然后执行 a 以确认它是正确的数据类型。CHARINDEXLENSUBSTRINGTRY_CAST
| 编号 | 字符串 | 校长 ID | 帐户 ID |
|---|---|---|---|
| 1 | https://database.abcd.com/Pages/Principal 详细信息.aspx？PrincipalID=123456789& | 123456789 | 零 |
| 2 | https://database.abcd.com/Pages/AccountDetails.aspx?AccountID=234567& | 零 | 234567 |
| 3 | https://database-db-account-docmgmt.abcd.com/accounts/123456/documents | 零 | 123456 |
| 4 | https://database.db.abcd.com/principals/987654321/home | 987654321 | 零 |
| 5 | https://database.abcd.com/Pages/AccountDetails.aspx?AccountID=654321& | 零 | 654321 |
| 6 | https://database.abcd.com/Pages/Principal 详细信息.aspx？PrincipalID=234567890&AccountID=345678& | 234567890 | 345678 |
| 7 | https://database-db-account-docmgmt.abcd.com/accounts/345679/documents | 零 | 345679 |
评论
1赞
siggemannen
3/16/2023
漂亮而简单的答案
0赞
Ricky
3/16/2023
希望我能刷新我的浏览器帕特里克更优雅的解决方案
0赞
Ricky
3/16/2023
#2
这是我想出的，创建了一个临时表和 case 语句来定义字符串类型，并且可以选择返回以提取帐户 ID 和主体 ID。不是很优雅，但似乎有效。希望这对某人有所帮助，有点不想在一段时间内看到另一个字符索引。
IF OBJECT_ID('tempdb..#string') IS NOT NULL
DROP TABLE ##string;
SELECT
a.Id
,(CASE
WHEN a1_stop > a1_start
THEN SUBSTRING(a.string,a1_start,a1_stop-a1_start+1)
WHEN a2_stop > a2_start
THEN SUBSTRING(a.string,a2_start,a2_stop-a2_start+1)
ELSE NULL
END) as Account_Id
,(CASE
WHEN p1_stop > p1_start
THEN SUBSTRING(a.string,p1_start,p1_stop-p1_start+1)
WHEN p2_stop > p2_start
THEN SUBSTRING(a.rl,p2_start,p2_stop-p2_start+1)
WHEN p3_stop > p3_start
THEN SUBSTRING(a.string,p3_start,p3_stop-p3_start+1)
ELSE NULL
END) as Principal_Id
into #string
FROM database.dbo.table as a
LEFT JOIN
(SELECT
Id
,SUBSTRING(string,1,36) as string_type
,(CASE
WHEN CHARINDEX('accounts',string) > 0
THEN CHARINDEX('/',string,CHARINDEX('accounts',string))+1
ELSE NULL
END) as a1_start
,(CASE
WHEN CHARINDEX('accounts',string) > 0
THEN CHARINDEX('/',string,CHARINDEX('/',string,CHARINDEX('accounts',string))+1)-1
ELSE NULL
END) as a1_stop
,(CASE
WHEN CHARINDEX('principals',string) > 0
THEN CHARINDEX('/',string,CHARINDEX('principals',string))+1
ELSE NULL
END) as p1_start
,(CASE
WHEN CHARINDEX('principals',string) > 0
THEN CHARINDEX('/',string,CHARINDEX('/',string,CHARINDEX('principals',string))+1)-1
ELSE NULL
END) as p1_stop
,(CASE
WHEN CHARINDEX('accountid',string) > 0
THEN CHARINDEX('=',string,CHARINDEX('accountid',string))+1
ELSE NULL
END) as a2_start
,(CASE
WHEN CHARINDEX('accountid',string) > 0 AND CHARINDEX('&',string) > 0
THEN CHARINDEX('&',string,CHARINDEX('=',string,CHARINDEX('accountid',string))+1)-1
WHEN CHARINDEX('accountid',string) > 0 AND CHARINDEX('&',string) = 0
THEN LEN(string)
ELSE NULL
END) as a2_stop
,(CASE
WHEN CHARINDEX('principalid',string) > 0
THEN CHARINDEX('=',string,CHARINDEX('principalid',string))+1
ELSE NULL
END) as p2_start
,(CASE
WHEN CHARINDEX('principalid',string) > 0 AND CHARINDEX('&',string) > 0
THEN CHARINDEX('&',string,CHARINDEX('=',string,CHARINDEX('principalid',string))+1)-1
WHEN CHARINDEX('principalid',string) > 0 AND CHARINDEX('&',string) = 0
THEN LEN(string)
ELSE NULL
END) as p2_stop
,(CASE
WHEN CHARINDEX('principal_id',string) > 0
THEN CHARINDEX('=',string,CHARINDEX('principal_id',string))+1
ELSE NULL
END) as p3_start
,(CASE
WHEN CHARINDEX('principal_id',string) > 0
THEN LEN(string)
ELSE NULL
END) as p3_stop
FROM database.dbo.table as table
) as b
ON a.Id = b.Id;
select id,
account_id,
principal_id
from #database
评论