为了简洁起见，计算CROSS APPLY中前两个下划线的位置。
在SQL Server中，我有一列包含一个格式为yyyy_d__m_(randominteger(_(randomnteger(_blahblahblah字符串的字符串。
所以
| 列 |
|---|
| 2021_28_6_42_blashblashblash |
| 2021_8_12_17_4_blahblahblah |
DECLARE @text VARCHAR(200) = '2021_8_12_17_4_blahblahblahblah';
-- https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string
with T as (
select 0 as row, charindex('_', @text) pos, @text as txt, 1 as occurance
union all
select pos + 1, charindex('_', @text, pos + 1), @text, occurance+1
from T
where pos > 0
)
select
@text, pos , occurance, substring(@text,0,pos) as "DATE"
from T
where occurance=3
- 首先我声明一个变量(名为
@text( - 在stackoverflow的一点帮助下，链接是使用谷歌的结果之一：对于tsl，在字符串中查找字符，以及一些小的更改
结果：
pos occurance DATE
--------------------------------- ----------- ----------- ------------
2021_8_12_17_4_blahblahblahblah 10 3 2021_8_12
其中DATE下的列是您要查找的文本。
附言：用这个代码创建一个函数会更漂亮，但是。。。。😉
select datefromparts(substring(t.d, 1, i1 - 1),
substring(t.d, i2 + 1, charindex('_', t.d, i2 + 1) - i2 - 1),
substring(t.d, i1 + 1, i2 - i1 - 1)
) dt
from (
values
('2021_8_12_17_4_blahblahblahblah'),
('2021_28_6_42_blahblahblahblah')
) t(d) -- your table here
cross apply(
select charindex('_', t.d, 1) i1, charindex('_', t.d, charindex('_', t.d, 1) + 1) i2
) t2
还有一个带有JSON 的选项
Select A.*
,AsDate = datefromparts(JSON_VALUE(S,'$[0]')
,JSON_VALUE(S,'$[2]')
,JSON_VALUE(S,'$[1]')
)
From YourTable A
Cross Apply ( values ( '["'+replace([column],'_','","')+'"]' ) ) B(S)
结果
column AsDate
2021_28_6_42_blahblahblahblah 2021-06-28
2021_8_12_17_4_blahblahblahblah 2021-12-08
注意：如果字符串中偶然出现双引号等，则可能需要应用string_escape()
...
Cross Apply ( values ( '["'+replace(STRING_ESCAPE([column],'json'),'_','","')+'"]' ) ) B(S)
这是一个非常糟糕的设计。您仍然可以将其转换为日期，但如果性能有问题，那么我将使用CLR函数。
create table baddata (bad varchar(100));
insert into baddata (bad) values
('2021_28_6_42_blahblahblahblah'),
('2021_8_12_17_4_blahblahblahblah');
with bd (bad,v,rn) as
(
select bad,
cast(v as int),
row_number() over (partition by bad order by bad)
from baddata
cross apply (select top(3) value
from string_split(baddata.bad,'_')) t(v)
),
ydm (bad, y,d,m) as
(
select bad,
sum(cast(case when rn=1 then v end as int)) y,
sum(cast(case when rn=2 then v end as int)) d,
sum(cast(case when rn=3 then v end as int)) m
from bd
group by bad
)
select bad, datefromparts(y,m,d) [date]
from ydm;
您需要计算下划线在前3个位置的位置。下面的工作原理是通过连续交叉APPLY'ing来获得CHARDINDEX位置。像这样的
select datefromparts(ci2.dt_yr, ci4.dt_mo, ci3.dt_day) dt_from_parts
from (values ('2021_8_12_17_4_blahblahblahblah'),
('2021_28_6_42_blahblahblahblah')) string(dt)
cross apply (values (charindex('_', string.dt, 1))) ci1(loc)
cross apply (values (charindex('_', string.dt, ci1.loc+1),
left(string.dt, ci1.loc-1))) ci2(loc, dt_yr)
cross apply (values (charindex('_', string.dt, ci2.loc+1),
substring(string.dt, ci1.loc+1, (ci2.loc-ci1.loc)-1))) ci3(loc, dt_day)
cross apply (values (substring(string.dt, ci2.loc+1, (ci3.loc-ci2.loc)-1))) ci4(dt_mo);
dt_from_parts
2021-12-08
2021-06-28
假设这段代码很难看，而且太多了，无法查看。它可以被提取到SCHEMABOUND内联表值函数中，您可以使用CROSS APPLY来简化查询。
drop function if exists dbo.test_fnExtractDate;
go
create function dbo.test_fnExtractDate(
@input_string varchar(64))
returns table with schemabinding as return
select datefromparts(ci2.dt_yr, ci4.dt_mo, ci3.dt_day) dt_from_parts
from (values (@input_string)) string(dt)
cross apply (values (charindex('_', string.dt, 1))) ci1(loc)
cross apply (values (charindex('_', string.dt, ci1.loc+1),
left(string.dt, ci1.loc-1))) ci2(loc, dt_yr)
cross apply (values (charindex('_', string.dt, ci2.loc+1),
substring(string.dt, ci1.loc+1, (ci2.loc-ci1.loc)-1))) ci3(loc, dt_day)
cross apply (values (substring(string.dt, ci2.loc+1, (ci3.loc-ci2.loc)-1))) ci4(dt_mo);
go
然后查询可能看起来像这个
select fex.*
from (values ('2021_8_12_17_4_blahblahblahblah'),
('2021_28_6_42_blahblahblahblah')) string(dt)
cross apply dbo.test_fnExtractDate(string.dt) fex
dt_from_parts
2021-12-08
2021-06-28