如何从SQL Server 2005中的字符串/文本字段中提取日期字段 [英] How to extract date fields from string/text field in sql server 2005

问题描述

在表中归档了一个文本，称为描述.当使用sql server 2005存储过程出现〜"字符时，我想从该字符串中提取两个日期字段.在这种情况下请帮助我.

示例:字符串:''长期租金;10/1/2012 ~ 10/31/2012''.在出现~运算符时，我希望使用起始日期:20121001和最新日期:20121031.

There is a text filed in a table called as description. I would like to extract two date fields from this string when there is an occurrence of ''~'' character using sql server 2005 stored procedure. Help me out in this case.

Example: string: ''长期租金;10/1/2012 ~ 10/31/2012''. At occurrence of ~ operator I would like to have from-date: 20121001 and to-date: 20121031.

推荐答案

最简单的方法是使用正则表达式.由于SQL Server默认情况下不支持正则表达式，因此您必须使用.net集成用户功能对其进行扩展.确实并不复杂.请参阅这两篇文章: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx [ ^ ]， MS SQL Server 2005/2008中的正则表达式 [ ^ ].
如果必须定期执行此任务，建议您根据上述方法之一实现两个功能，该功能直接将最后一个日期的第一个日期提取到sql日期.

The easiest way is to use regular expressions. As SQL server does not support regular expressions by default, you have to extend it using .net integrated user function. It is really not complicated. See these two articles: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx[^], Regular Expressions in MS SQL Server 2005/2008[^].
If this task has to be done regularly, I suggest you implement two functions based on one of the above, that extracts first of last dates directly to sql date.

使用CHARINDEX [左 [^ ]或右 [订阅 [ ^ ]函数返回字符串的一部分.

例如:

Use CHARINDEX[^] function to find ~ and then LEFT[^] or RIGHT[^] or SUBSTRING[^] function to return the part of string.

For example:

DECLARE @inputstring NVARCHAR(100)
DECLARE @pos INT
DECLARE @fromdate NVARCHAR(10)
DECLARE @todate NVARCHAR(10)

SET @inputstring = '长期租金;10/01/2012 ~ 10/31/2012'
SET @pos = CHARINDEX('~', @inputstring)

IF @pos >0
    BEGIN
        SET @fromdate = SUBSTRING(@inputstring,@pos-11,10)
        SET @todate = SUBSTRING(@inputstring,@pos+1,10)
        SELECT @fromdate as [from-date], @todate as [to-date]
    END
ELSE --@pos =0
    PRINT 'There''s no matches!'

嗨..，

试试这个批次

Hi ..,

try this batch

DECLARE @String VARCHAR(200) = '长期租金;10/1/2012 ~ 10/31/2012'

SET @String = SUBSTRING(@String,CHARINDEX(';',@String,0)+1,LEN(@String))

SELECT SUBSTRING(@String,0,CHARINDEX('~',@String,0)) [From-Date], 
SUBSTRING(@String,CHARINDEX('~',@String,0)+1,LEN(@String)) [To-Date]

谢谢

Thank you

这篇关于如何从SQL Server 2005中的字符串/文本字段中提取日期字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！