CODEWITHSUNDEEP
sql-server
Jeremy
Jeremy

Reputation: 1

Parsing Attribute Data from Column- SQL Server

I have a table with column "Long Description" typically the data looks like the following.

Foundation area wall, 12" H. x 20" W. x 8" projection. Galvanized. Refer to model No. SV208 (SKU 100002) for foundation area wall cover. No. FV208-12: Height: 12", Width: 20", Projection: 8", Type: Foundation Area Wall, Material: Galvanized, Pkg Qty: 1

What I am trying to do is parse out the end attributes. For example after "area wall cover." and beginning with "No." I'd like to extract the following. (Below) Some things to note. The string '. No.' always begins the attributes in this column. All attributes are separated by columns. The attribute names differ and the amount of attributes per product also differ. Is there a way this can be done with T-SQL? 

No. FV208-12:
Height: 12"
Width: 20"
Projection: 8"
Type: Foundation Area Wall
Material: Galvanized
Pkg Qty: 1

Upvotes: 0

Views: 40

Answers (1)

alan
alan

Reputation: 6943

You can use a variation of the following to achieve what I believe you're attempting to achieve:

DECLARE @StartAttributesKey VARCHAR(50) = 'area wall cover. ' ,
    @LongDescription VARCHAR(MAX) = 'Foundation area wall, 12" H. x 20" W. x 8" projection. Galvanized. Refer to model No. SV208 (SKU 100002) for foundation area wall cover. No. FV208-12: Height: 12", Width: 20", Projection: 8", Type: Foundation Area Wall, Material: Galvanized, Pkg Qty: 1';

SELECT  REPLACE(SUBSTRING(@LongDescription, CHARINDEX(@StartAttributesKey, @LongDescription, 0) + LEN(@StartAttributesKey),
                          LEN(@LongDescription) - CHARINDEX(@StartAttributesKey, @LongDescription, 0)), ',', CHAR(10));

Using this in a query would be similar to:

DECLARE @StartAttributesKey VARCHAR(50) = 'area wall cover. ' 
SELECT REPLACE(SUBSTRING(LongDescription, CHARINDEX(@StartAttributesKey, LongDescription, 0) + LEN(@StartAttributesKey),
                              LEN(LongDescription) - CHARINDEX(@StartAttributesKey, LongDescription, 0)), ',', CHAR(10))
FROM [someTable] WHERE ID = 1

If you copy (or print) the result, you will see each attribute on a separate line.

Upvotes: 1

Related Questions