GrassHopper (5/31/2013)
I have a comments field that has certain values I want to extract into it's own field(new must be created). What is the syntax for doing this?the data looks like this:
Comments
5321025 HOLLAND BL 60X210 20 Unts $5.91 Unt
5321032 HOLLAND BL 60X210 6 Unts $5.91 Unt
5321124 HOLLAND BL 120X210 13 Unts $8.64 Unt
it depends;
your limited example data implies an order to the comments, so you could use the DelimitedSplit8K function[/url]
to split the values out to related rows, and insert/update based on that.
With MyCTE (Comments)
AS
(
SELECT '5321025 HOLLAND BL 60X210 20 Unts $5.91 Unt' UNION ALL
SELECT '5321032 HOLLAND BL 60X210 6 Unts $5.91 Unt' UNION ALL
SELECT '5321124 HOLLAND BL 120X210 13 Unts $8.64 Unt'
)
SELECT
MyCTE.*,
T1.*
FROM MyCTE
CROSS APPLY dbo.DelimitedSplit8K(Comments,' ') T1
Lowell