GrassHopper (5/31/2013)
Sorry, I forgot to specify I only need the units and the amounts. iecomments:
5321063 HOLLAND BL 90X210 10 Unts $7.27 Unt
5321070 HOLLAND BL 90X210 1 Unts $7.27 Unt
5321124 HOLLAND BL 120X210 13 Unts $8.64 Unt
my 2 new fields should be :
Units UnitPrice
10 $7.27
1 $7.27
13 $8.64
As Lowell already stated, if the comments have a consistent pattern you can use the DelimitedSplit8K function. Just need to add a where clause.
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
where ItemNumber in (5, 7)
If your Comments column does not fit a consistent pattern this can be exponentially more difficult depending on the actual contents.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/