SQL从字符串的任何部分删除多个时间戳 [英] SQL remove multiple time stamp from any part of a string

问题描述

我有一个包含多个日期时间戳的文本字段，我只需要删除时间戳。在此字段中也有带有回车符的文本。我想保留文本，日期和回车符。

I have a text field that contains multiple datetime stamps and I need to remove just the time stamp. There is also text with carriage returns in this field. I want to maintain text, date, and carriage returns.

任何人都对此有经验，可以分享选择以完成此工作吗？

Anyone have experience with this and can share the select to accomplish this?

以下是文本字段的示例：

Here is an example of the textfield :

[10/19/2015 5:24:02 PM Tech1] Repaired heaters [10/21/2015 8:36:28 AM Tech1] CHECKED ALL HEATER OPERATION  

任何见识将不胜感激。预先感谢您的帮助！

Any insight would be greatly appreciated. Thanks in advance for your help!

推荐答案

更新：误解了需求，根据下面的评论更新了我的解决方案。

UPDATE: Misunderstood the requirement, updated my solution based on comments below.

为此，您将需要 PatternSplitCM 。

declare @table table (someid int identity, somestring varchar(1000));
insert @table(somestring) values 
('[10/19/2015 5:24:02 PM Tech1] Repaired heaters [10/21/2015 8:36:28 AM Tech1] CHECKED ALL HEATER OPERATION'),
('[02/28/2015 5:24:02 PM Tech1] Repaired more stuff [12/01/2015 2:36:28 AM Tech1] CHECKED ALL HEATER OPERATION');

with parseMe as
(
  select 
    someid, 
    ItemNumber,
    Item = case 
           when Item like '[0-9]:[0-9][0-9]:[0-9][0-9]' then '<exclude>' 
           else replace(replace(item, ' AM ',''), ' PM ', '') end
  from @table t
  cross apply dbo.PatternSplitCM(t.somestring, '[0-9:/]')
)
select
  someid,
  newString = 
    replace((
      select item+''
      from parseMe p2 
        where p1.someid = p2.someid 
      order by ItemNumber
      for xml path(''), type
    ).value('.', 'varchar(1000)'), '<exclude>','')
from parseMe p1
group by someid;

这篇关于SQL从字符串的任何部分删除多个时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！