How to get date from String filed?

  • Here you are. Thank you.

    USE [dev_Volusion]

    GO

    /****** Object: Table [dbo].[Products] Script Date: 09/28/2011 13:50:04 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Products](

    [ProductCode] [varchar](30) NOT NULL,

    [Vendor_PartNo] [varchar](30) NULL,

    [ProductName] [varchar](255) NULL,

    [DisplayBeginDate] [smalldatetime] NULL,

    [DisplayEndDate] [smalldatetime] NULL,

    [HideProduct] [varchar](1) NULL,

    [StockStatus] [int] NULL,

    [LastModified] [smalldatetime] NULL,

    [Options_Cloned_From] [varchar](30) NULL,

    [Photos_Cloned_From] [varchar](30) NULL,

    [Share_StockStatus_With] [varchar](30) NULL,

    [LastModBy] [int] NULL,

    [IsChildOfProductCode] [varchar](30) NULL,

    [ProductID] [int] NOT NULL,

    [IsChildOfProductCode_ProductID] [int] NULL,

    [Options_Cloned_From_ProductID] [int] NULL,

    [Photos_Cloned_From_ProductID] [int] NULL,

    [Share_StockStatus_With_ProductID] [int] NULL,

    [ProductPopularity] [int] NULL,

    [HomePage_Section] [int] NULL,

    [AutoDropShip] [varchar](1) NULL,

    [DoNotAllowBackOrders] [varchar](1) NULL,

    CONSTRAINT [UNIQUE_Products_ProductsID] PRIMARY KEY CLUSTERED

    (

    [ProductID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],

    CONSTRAINT [UNIQUE_Products_ProductsCode] UNIQUE NONCLUSTERED

    (

    [ProductCode] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Fawad Rashidi
    www.fawadafr.com

  • Try this

    SELECT ProductCode,ProductName,RTRIM(LTRIM(Substring(deliveryText, start -1, length - start - fromEnd + 3))) AS [extracted]

    FROM (

    SELECT ProductName,MAX(PATINDEX([Matched], ProductName)) AS start

    ,MAX(PATINDEX(ReverseMatch, reverse(tableText + ' ')) - 1) AS fromEnd

    ,len(ProductName + '|') - 1 AS [length]

    ,ProductName AS deliveryText

    ,ProductCode

    FROM (

    SELECT '%[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]</span>%'

    ,'%<naps/>[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1]%'

    UNION ALL

    SELECT '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'

    ,'%[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9]%'

    ) AS f([Matched], ReverseMatch)

    CROSS JOIN Products

    GROUP BY ProductName

    ) search

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I get this error message:

    Msg 207, Level 16, State 1, Line 15

    Invalid column name 'tableText'.

    Here is the SQL statement I ran:

    --Some test data first

    DECLARE @TABLE AS TABLE (tableText VARCHAR(1000))

    INSERT INTO @TABLE

    SELECT ProductName FROM Products WHERE ProductName LIKE '%Availability%'

    UNION ALL SELECT ProductName FROM Products WHERE ProductName LIKE '%Availability%'

    --Query

    SELECT ProductCode,ProductName,RTRIM(LTRIM(Substring(deliveryText, start -1, length - start - fromEnd + 3))) AS [extracted]

    FROM (

    SELECT ProductName,MAX(PATINDEX([Matched], ProductName)) AS start

    ,MAX(PATINDEX(ReverseMatch, reverse(tableText + ' ')) - 1) AS fromEnd

    ,len(ProductName + '|') - 1 AS [length]

    ,ProductName AS deliveryText

    ,ProductCode

    FROM (

    SELECT '%[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]</span>%'

    ,'%<naps/>[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1]%'

    UNION ALL

    SELECT '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'

    ,'%[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9]%'

    ) AS f([Matched], ReverseMatch)

    CROSS JOIN Products

    GROUP BY ProductName

    ) search

    Fawad Rashidi
    www.fawadafr.com

  • Looks like I missed a column named tabletext - replace it with ProductName and the script should run.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Great. I just replaced the text. I am now getting this error message:

    Msg 8120, Level 16, State 1, Line 12

    Column 'Products.ProductCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Thanks for all your help!

    Fawad Rashidi
    www.fawadafr.com

  • SELECT ProductCode,ProductName,RTRIM(LTRIM(Substring(deliveryText, start -1, length - start - fromEnd + 3))) AS [extracted]

    FROM (

    SELECT ProductName,MAX(PATINDEX([Matched], ProductName)) AS start

    ,MAX(PATINDEX(ReverseMatch, reverse(ProductName + ' ')) - 1) AS fromEnd

    ,len(ProductName + '|') - 1 AS [length]

    ,ProductName AS deliveryText

    ,ProductCode

    FROM (

    SELECT '%[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]</span>%'

    ,'%<naps/>[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1]%'

    UNION ALL

    SELECT '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'

    ,'%[0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9]%'

    ) AS f([Matched], ReverseMatch)

    CROSS JOIN Products

    GROUP BY ProductName,ProductCode

    ) search

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Great. Works fine now. I also added a WHERE clause so I only retrieve the records with the availability dates in them...

    WHERE ProductName LIKE '%Availability%'

    Thank you very much for all your help! I truly appreciate it.

    Fawad Rashidi
    www.fawadafr.com

  • You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply