How to extract dates from a varchar string

How to extract dates开发者_如何学JAVA from a varchar string;

Format of data:

    Rates from 16th April  2011 to 31st Aug 2011

I want output as '16/04/2011' and '31/08/2011'

Regards, Nitin

To do not play with day end part like "d","st" etc, it is better to split your string into words and then analyse it. Double spaces being removed at the beginning.

 DECLARE @s NVARCHAR(MAX)= 'Rates from 16th April  2011 to 2st Aug 2011'
SET @s = REPLACE(@s, '  ', ' ')
DECLARE @BegDay VARCHAR(2)= '' ,
    @begMonth NVARCHAR(100) ,
    @BegYear VARCHAR(4)
DECLARE @EndDay VARCHAR(2) ,
    @EndMonth NVARCHAR(100) ,
    @EndYear VARCHAR(4)
DECLARE @BegDate DATETIME ,
    @EndDate DATETIME
DECLARE @Words TABLE
    (
      Word NVARCHAR(1000) ,
      WordNr INT
    ) ;
WITH    c ( Char, Pos, WordNr )
          AS ( SELECT   SUBSTRING(@s, 1, 1) CHAR ,
                        1 ,
                        CAST(0 AS BIGINT)
               UNION ALL
               SELECT   SUBSTRING(@s, pos + 1, 1) CHAR ,
                        pos + 1 ,
                        CASE WHEN SUBSTRING(@s, pos + 1, 1) = SPACE(1)
                             THEN c.WordNr + 1
                             ELSE c.WordNr
                        END
               FROM     c
               WHERE    pos < LEN(@s)
             )
    INSERT  INTO @Words
            ( Word ,
              WordNr 
            )
            SELECT  RTRIM(LTRIM(SUBSTRING(@s, c.Pos,
                                          ISNULL(c2.Pos, LEN(@s)) - c.Pos + 1))) ,
                    c.WordNr + 1
            FROM    ( SELECT    *
                      FROM      c
                    ) c
                    LEFT OUTER JOIN C C2 ON c2.WordNr = c.WordNr + 1
                                            AND c2.Char = ' '
            WHERE   c.char = ' '
                    OR c.Pos = 1 ;
WITH    c ( Word, [CHAR], Pos )
          AS ( SELECT   T.Word ,
                        CAST(SUBSTRING(T.Word, 1, 1) AS NVARCHAR(100)) ,
                        1
               FROM     @Words T
               WHERE    WordNr = 3
               UNION ALL
               SELECT   c.Word ,
                        CAST(SUBSTRING(c.Word, 1, pos + 1) AS NVARCHAR(100)) ,
                        pos + 1
               FROM     c
               WHERE    ISNUMERIC(SUBSTRING(C.Word, c.pos + 1, 1)) = 1
             )
    SELECT TOP 1
            @BegDay = char
    FROM    c
    ORDER BY pos DESC

SET @begMonth = ( SELECT    T.Word
                  FROM      @Words T
                  WHERE     T.WordNr = 4
                )
SET @BegYear = ( SELECT T.Word
                 FROM   @Words T
                 WHERE  T.WordNr = 5
               )

SET @BegDate = @BegDay + '' + @begMonth + @Begyear ;
WITH    c ( Word, [CHAR], Pos )
          AS ( SELECT   T.Word ,
                        CAST(SUBSTRING(T.Word, 1, 1) AS NVARCHAR(100)) ,
                        1
               FROM     @Words T
               WHERE    WordNr = 7
               UNION ALL
               SELECT   c.Word ,
                        CAST(SUBSTRING(c.Word, 1, pos + 1) AS NVARCHAR(100)) ,
                        pos + 1
               FROM     c
               WHERE    ISNUMERIC(SUBSTRING(C.Word, c.pos + 1, 1)) = 1
             )
    SELECT TOP 1
            @EndDay = char
    FROM    c
    ORDER BY pos DESC

SET @EndMonth = ( SELECT    T.Word
                  FROM      @Words T
                  WHERE     T.WordNr = 8
                )
SET @EndYear = ( SELECT T.Word
                 FROM   @Words T
                 WHERE  T.WordNr = 9
               )

SET @EndDate = @EndDay + '' + @EndMonth + @Endyear

SELECT  @BegDate Begindate ,
        @EndDate EndDate