在字符之间提取数据SQL
我正在尝试检索字符之间的随机数据
数据示例:
ABC-33-Ha8o89-00
ABC-232-Ui7380-000
在上面的示例中,我正在尝试获取Ha8o89和Ui7380。基本上是从左起两个破折号和从右起一个破折号之后的所有数据。
解决方案
给定此数据:
CREATE TABLE dbo.RandomData(StringValue varchar(128));
INSERT dbo.RandomData(StringValue) VALUES
('ABC-33-Ha8o89-00'),
('ABC-232-Ui7380-000');
假设您声明始终有三个破折号:
,这是一种快捷而肮脏的方法SELECT StringValue,
Parsed = PARSENAME(REPLACE(StringValue,'-','.'), 2)
-- parsename starts right
FROM dbo.RandomData;
2016+可以使用OPENJSON
:
SELECT r.StringValue, Parsed = j.value
FROM dbo.RandomData AS r
CROSS APPLY OPENJSON ('["'
+ REPLACE(r.StringValue, '-', '","') + '"]') AS j
WHERE [key] = 2; -- keys are 0-based
在任何版本上,您都可以使用有序拆分函数(有几十个示例),这里是我从this article和this earlier answer中抓取的一个:
CREATE FUNCTION dbo.SplitOrdered
(
@list nvarchar(max),
@delim nvarchar(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
WITH w(n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0)) w(n)),
k(n) AS (SELECT 0 FROM w a, w b),
r(n) AS (SELECT 0 FROM k a, k b, k c, k d, k e, k f, k g, k h),
p(n) AS (SELECT TOP (COALESCE(LEN(@list), 0))
ROW_NUMBER() OVER (ORDER BY @@SPID) -1 FROM r),
spots(p) AS
(
SELECT n FROM p
WHERE (SUBSTRING(@list, n, LEN(@delim + 'x') - 1) LIKE @delim OR n = 0)
),
parts(p,val) AS
(
SELECT p, SUBSTRING(@list, p + LEN(@delim + 'x') - 1,
LEAD(p, 1, 2147483647) OVER (ORDER BY p) - p - LEN(@delim))
FROM spots AS s
)
SELECT listpos = ROW_NUMBER() OVER (ORDER BY p),
Item = LTRIM(RTRIM(val))
FROM parts
);
则查询为:
SELECT r.StringValue, Parsed = s.Item
FROM dbo.RandomData AS r
CROSS APPLY dbo.SplitOrdered(r.StringValue, '-') AS s
WHERE s.listpos = 3; -- listpos is 1-based
此db<>fiddle中的所有三个示例。
相关文章