Solved

Extract TEXT from a STRING field separated by COMMAs....?

Posted on 2008-10-16
9
576 Views
Last Modified: 2013-11-05
Experts:

My frield looks like this:

234569,CPU,JOHN, SMITH
234570,CPU,MARY,JONES
234571,PRINTER,BILL,JENKINS
234572,CPU,TAMMY,MORRIS
234573,MONITOR,JIM,JAMES

I need to EXTRACT each piece of this STING and place it in its own column.

How can I accomplish this?

Thanks
M
0
Comment
Question by:MIKE
  • 5
  • 2
  • 2
9 Comments
 
LVL 1

Expert Comment

by:patricka_0377
Comment Utility
if its a csv (comma separated variables)? just create a file call it filename.csv open it up in excel it will do the rest for you
0
 
LVL 17

Author Comment

by:MIKE
Comment Utility
NO...no.....no.
   
  : )

This is a field in my DATATABLE in SQL Server 2005.

I need to use the separate pieces in OTHER logic, so first of all, I need to know HOW to create a Stored Proc and/or Function that will extract each piece so that I can go to my next step and use the pieces AS IF they are Columns of data.

Thanks
M
0
 
LVL 1

Expert Comment

by:patricka_0377
Comment Utility
0
 
LVL 17

Author Comment

by:MIKE
Comment Utility
For example, a start would be HOW do I get this from this field:

CPU
CPU
PRINTER
CPU
MONITOR

This would be the SECOND section of data,...right after the first Comma.

Thx
M
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 17

Author Comment

by:MIKE
Comment Utility
I'm doing THIS to get the first piece:

select LEFT(myField,6) as [OrderID]
from myTable

The aboev SQL gives me this:

234569
234570
234571
234572
234573

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Try this.  I think this is the right syntax for the second field.

select substring(MyField, charindex(',',myfield)+1, charindex(',',myfield, charindex(',',myfield)+1)-1)
from MyTable
0
 
LVL 17

Author Comment

by:MIKE
Comment Utility
Almost....

Returns this:

CPU,JOHN,
CPU,MARY,
PRINTER,BILL,J
CPU,TAMMY
MONITOR,JIM,J

So... it is returning the correct STARTING like CPU and PRINTER, but it also returns the NEXT 6 characters...

I only need what is between the 1st and 2nd commas...

Thx
M
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
Comment Utility
Here you go:

Just replace your table name and remove everything after the from.  I just threw your example into a derived table for testing.
select substring(myfield, charindex(',',myfield)+1,charindex(',',myfield,charindex(',',myfield)+1)-(charindex(',',myfield)+1))
 

from 

(select '234569,CPU,JOHN, SMITH' as myfield

union select '234570,CPU,MARY,JONES'

union select '234571,PRINTER,BILL,JENKINS'

union select '234572,CPU,TAMMY,MORRIS'

union select '234573,MONITOR,JIM,JAMES'

) a

Open in new window

0
 
LVL 17

Author Closing Comment

by:MIKE
Comment Utility
THANKS
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now