I came up with an easier way:

Check this out:

select productcode,

(replace(substring(p2.productname,patindex('%Availability: %',p2.productname)+14,100),'</span>','')) AS Availability

from products

Wain:

How would I specify the 5K products in the SELECT statement and then UNION ALL after each product name following Chris' example?

Wain:

How about running Chris' script against the entire product table with over 5,000 products. So, the final data grid should include the product code, product name, and availability date (extracted...

Chris:

In your examples you are using the five product names I had provided. What would be the best way if I am dealing with 5,000+ products?

Hi bhuvneshk and Robert:

All product names end with a date string (e.g. <span style="color:#f00;">Availability: 6/27/2012</span>)

No, each product name string can only contain one date.

Is it possible to get the date...

@bitbucket-25253:

Thanks for the feedback. Yes, it is extremely import for me to remove all the extra trailing spaces as they break the hyperlinks. The [doc_path] field contains the...

Lowell:

I am still having the space issue. Do you think I should export all the data and re-import them now the field properties are fixed? I only have about 500...

I just ran the script the ALTER the column. Here is the new table structure:

/****** Object: Table [dbo].[document_control] Script Date: 02/07/2012 09:55:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER...

@Fahey:

Thanks for the feedback. I tried executing the following SQL statement but was unable to change the field type.

ALTER TABLE [dbo].[document_control] MODIFY COLUMN [doc_path] [varchar](256) NULL

Could you please help me...

@lowell:

Thanks for the feedback. Yes, I tried the LTRIM() and RTRIM() function but I still have the spaces at the end of [doc_title] field. Please see below:

id ...

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...

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...

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...

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)...

Yes, I made the following change to the code provided. However, how would I add the product code for each date?

--Some test data first

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

INSERT INTO...