Need Help Splitting Strings, evaluating numerical parts and concatenating results

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)
JapsterexAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT
    Job, MIN(Serial) AS Start_Serial, MAX(Serial) AS End_Serial
FROM dbo.tablename
GROUP BY Job
0
 
JapsterexAuthor Commented:
brilliant

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

Excellent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.