Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need Help Splitting Strings, evaluating numerical parts and concatenating results

Posted on 2013-05-29
2
Medium Priority
?
231 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

972 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