英文:
Perform Substring after a specific phrase in a string
问题
我正在尝试从SQL表中的数百行数据中提取日期。我要提取的字段是一个VARCHAR 'comment'字段，所以用户可以输入任何内容。
这些字符串中的一致短语是"Date Out"。
我要做的是在字符串中搜索这个短语，并提取其后的日期部分。
所以如果我有：
Hello Date Out yyyy-MM-dd
Hello, who are you Date Out yyyy-MM-dd
Hello how are you doing Date Out yyyy-MM-dd
What do you think you're doing Date Out yyyy-MM-dd
...
我该如何做呢？我以前从未需要在这种情况下使用CHARINDEX或Substring，所以我不太确定如何处理这个问题。
我尝试了一些方法，但目前还没有成功，例如：
string myString = "Hello how are you doing Date Out yyyy-MM-dd";
string toBeSearched = "Date Out";
string code = myString.Substring(myString.IndexOf(toBeSearched) + toBeSearched.Length);
string s = "Hello how are you doing Date Out yyyy-MM-dd";
var match = Regex.Match(s, "Date Out");
string src;
if (match.Success)
src = match.Groups[1].Value;
这里的第二个代码块返回match.Success = true，但字符串为空。
英文:
I'm trying to extract a date from hundreds of rows of data in a SQL table. The field I'm extracting from is a VARCHAR 'comment' field, so users can input anything.
The consistent phrase in these strings is "Date Out".
What I'm trying to do is search from this phrase in the string, and extract the date portion that follows.
So if I have:
Hello Date Out yyyy-MM-dd
Hello, who are you Date Out yyyy-MM-dd
Hello how are you doing Date Out yyyy-MM-dd
What do you think you're doing Date Out yyyy-MM-dd
...
How would I go about doing this? I've never had the need to do CHARINDEX or Substring on phrases like this, so I'm not sure how to approach this.
I've tried a few things but no luck so far, such as:
string myString = "Hello how are you doing Date Out yyyy-MM-dd";
string toBeSearched = "date";
string code = myString.Substring(myString.IndexOf(toBeSearched) + toBeSearched.Length);
string s = "Hello how are you doing Date Out yyyy-MM-dd";
//var match = Regex.Match(s, "Date Out=\"(.*?)\"");
var match = Regex.Match(s, "Date Out");
string src;
if (match.Success)
src = match.Groups[1].Value;
The 2nd code block here returns match.Success = true, but the string is blank.
答案1
得分: 1
select Substring(comments, p, 10) ExtractedString
from t
cross apply(values(9 + CharIndex('date Out', comments)))p(p);
英文:
A SQL Server solution would be a simple use of charindex and substring:
select Substring(comments, p, 10) ExtractedString
from t
cross apply(values(9 + CharIndex('date Out', comments)))p(p);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论