May 31, 2013 at 7:41 am
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
May 31, 2013 at 7:48 am
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
May 31, 2013 at 7:53 am
Sorry, I forgot to specify I only need the units and the amounts. ie
comments:
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
May 31, 2013 at 8:55 am
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/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy