Solved

Need Help Splitting Strings, evaluating numerical parts and concatenating results

Posted on 2013-05-29
2
221 Views
Last Modified: 2013-05-29
Hi,

I need to create a view based upon a SQL table that contains 2 string fields (Job, Serial) i.e. one job contains many serials in this table.

The Serial string consists of 4 alpha then 5 numeric. (e.g. ABDC12345)

The resulting View needs to have the following fields (Job, Start Serial, End Serial) for each Job number

Where Start Serial consists of the 4 alpha & the lowest 5 numeric for the given Job
Where End Serial consists of the 4 alpha & the highest 5 numeric for the given job

Any preceding zeros in the 5 numeric portion of the Serial string need to be maintained when the numeric portion is joined back to the resulting Start Serial & End Serial strings)

i.e. ABCD00001 not ABDC1 (resulting string must always be 9 chars in length)
0
Comment
Question by:Japsterex
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39204929
SELECT
    Job, MIN(Serial) AS Start_Serial, MAX(Serial) AS End_Serial
FROM dbo.tablename
GROUP BY Job
0
 

Author Comment

by:Japsterex
ID: 39204972
brilliant

didn't realize min and max would work with strings, I've been over complicating this somewhat.

Excellent
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 66
SqlDataBase 7 46
MS SQL / SQL Server Native Client -- how to prevent seeing other servers? 2 20
Sql Query 6 63
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
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 information from SQL Server on Database, Connection and Server properties

786 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