Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract values from varchar column Expand / Collapse
Author
Message
Posted Friday, May 31, 2013 7:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 15, 2016 7:52 AM
Points: 187, Visits: 528
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



Post #1458684
Posted Friday, May 31, 2013 7:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 14,469, Visits: 38,078
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
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1458690
Posted Friday, May 31, 2013 7:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 15, 2016 7:52 AM
Points: 187, Visits: 528
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





Post #1458693
Posted Friday, May 31, 2013 8:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:27 PM
Points: 15,987, Visits: 16,536
GrassHopper (5/31/2013)
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




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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1458740
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse