Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
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 @ 9:20 AM
Points: 12,916, Visits: 32,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 13,446, Visits: 12,308
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