Solved

Need Help Splitting Strings, evaluating numerical parts and concatenating results

Posted on 2013-05-29
2
220 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:
ScottPletcher 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

910 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

16 Experts available now in Live!

Get 1:1 Help Now