September 28, 2011 at 2:50 pm
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
September 28, 2011 at 2:59 pm
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
September 28, 2011 at 3:06 pm
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
September 28, 2011 at 3:09 pm
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
September 28, 2011 at 3:13 pm
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
September 28, 2011 at 3:22 pm
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
September 28, 2011 at 3:25 pm
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
September 28, 2011 at 3:28 pm
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