Solved

SQL query to retun only part of a piece of data

Posted on 2011-03-08
3
238 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 125 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:ewangoya
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Log size 3 20
Scheduled bat file step with psexec in SQLServer agent job only 2 21
xml files 7 29
sql 2016 data tools breakdown.. 1 15
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 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