?
Solved

SQL query to retun only part of a piece of data

Posted on 2011-03-08
3
Medium Priority
?
246 Views
Last Modified: 2012-05-11
I have a table in a database that contains data that looks like this:

10-25-665-123456
10-25-666-123548

The format of the data is always the same number of digits with the dashes always in the same place.  I am looking for a query that will only return certain parts of the data.  For example, I want the query to return only the first two digits, then then next two digits, then the next digits and so on so that my result from the query would contain separate columns with each of those pieces of data that is separated by the dashes.  Is this possible?  I know you can probably somehow use the Replace command but not sure how.
0
Comment
Question by:jwmillerill
3 Comments
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 35072890
Hi,
You can use the substring command.
Like:
SELECT SUBSTRING(myColumn,1,2) col1, SUBSTRING(myColumn,4,2) col2, SUBSTRING(myColumn,7,2) col3,
             SUBSTRING(myColumn,11,6) col4
FROM myTable
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35072894


SELECT SUBSTRING('10-25-665-123456', 1, 2) 'Part1',
       SUBSTRING('10-25-665-123456', 4, 2) 'Part2',
       SUBSTRING('10-25-665-123456', 7, 3) 'Part3',
       SUBSTRING('10-25-665-123456', 11, 6) 'Part4'
0
 
LVL 1

Author Closing Comment

by:jwmillerill
ID: 35072931
Brilliant.  Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

839 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