Extract values from varchar column

Question

Extract values from varchar column

GrassHopper

SSCarpal Tunnel

Points: 4473
More actions
May 31, 2013 at 7:41 am

#148167

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--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!

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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 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/