Trim numbers in SQL View

I am creating a view in SQL and want to only show the last 7 digits for a number field. The current format is "M01_001234567".

I just want it to show "1234567".

Any help is appreciated.

Thanks!!
srodgers45Asked:
Who is Participating?
 
IrogSintaCommented:
Try this:

ALTER VIEW schema_name.view_name
AS
SELECT     TOP (100) PERCENT dbo.shiftlookupalacityfdpt.shift, dbo.Completion_Status.Tdate, dbo.Completion_Status.CREATED, dbo.completion_status.RUN_NUMBER, Right(dbo.completion_status.RUN_NUMBER, 7) As RunNum, 
                      dbo.FDC_Trips.RunNumber, dbo.FDC_Trips.completedTime, dbo.FDC_Trips.completedByUser, dbo.Completion_Status.[Checked out By], 
                      dbo.FDC_Trips.status AS [Dup Status], dbo.Completion_Status.STATUS, dbo.Completion_Status.COMPLETED, dbo.Completion_Status.UNIT_VEHICLE, 
                      dbo.Completion_Status.Agency, dbo.Completion_Status.PCR_KEY, dbo.FDC_Trips.g2pcrid, dbo.Completion_Status.LAST_ACCESSED, dbo.Completion_Status.HEADER, 
                      dbo.Completion_Status.name, dbo.Completion_Status.LAST_ACCESSED_MACHINE
FROM         dbo.Completion_Status LEFT OUTER JOIN
                      dbo.FDC_Trips ON dbo.Completion_Status.RUN_NUMBER = dbo.FDC_Trips.RunNumber CROSS JOIN
                      dbo.shiftlookupalacityfdpt
WHERE     (dbo.Completion_Status.CREATED BETWEEN dbo.shiftlookupalacityfdpt.start_dtm AND dbo.shiftlookupalacityfdpt.end_dtm) AND 
                      (dbo.Completion_Status.Agency = 'Alameda City FD')

Open in new window

0
 
IrogSintaCommented:
How about Right(FieldName, 7)
0
 
srodgers45Author Commented:
If I want to create/insert a separate column within the view to always show both fields, the original and the trimmed version is that possible?

Thanks for the help!

SQL View
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
IrogSintaCommented:
Sure, it is. Let's say you want to insert if after the RUN_NUMBER column, the portion of your SQL should be something like this:

dbo.completion_status.RUN_NUMBER, Right(dbo.completion_status.RUN_NUMBER, 7) As RunNum,
0
 
srodgers45Author Commented:
how do I add that into column wiht that data into the existing View - Alter table? I am still learning this stuff :) Thanks
0
 
IrogSintaCommented:
Use the Alter View method.  See the following link:
http://msdn.microsoft.com/en-us/library/ms173846.aspx
0
 
srodgers45Author Commented:
I cannot seem to add that new column into the view using alter?  How do I just change the exisitng run nmber column to show the last 7 digits in the query. I know its the:  Right(FieldName, 7) I just don't now how to add that in.

Sorry, not a SQL expert. Just enough to be dangerous.

Thx
0
 
IrogSintaCommented:
Can you post the Alter View statement you used?
0
 
srodgers45Author Commented:
I dont think its correct

ALTER VIEW dbo.View_AlamedaCityFD_Completion ADD IncidentNumber1 VARCHAR (20)
0
 
IrogSintaCommented:
Did you look at the examples at the end of the link I gave you?  You pretty much have to include your whole SQL statement in there.
0
 
srodgers45Author Commented:
I did, i just don't know where to insert that in the statement- before, after, middle?

here is the current statement

SELECT     TOP (100) PERCENT dbo.shiftlookupalacityfdpt.shift, dbo.Completion_Status.Tdate, dbo.Completion_Status.CREATED, dbo.Completion_Status.RUN_NUMBER,
                      dbo.FDC_Trips.RunNumber, dbo.FDC_Trips.completedTime, dbo.FDC_Trips.completedByUser, dbo.Completion_Status.[Checked out By],
                      dbo.FDC_Trips.status AS [Dup Status], dbo.Completion_Status.STATUS, dbo.Completion_Status.COMPLETED, dbo.Completion_Status.UNIT_VEHICLE,
                      dbo.Completion_Status.Agency, dbo.Completion_Status.PCR_KEY, dbo.FDC_Trips.g2pcrid, dbo.Completion_Status.LAST_ACCESSED, dbo.Completion_Status.HEADER,
                      dbo.Completion_Status.name, dbo.Completion_Status.LAST_ACCESSED_MACHINE
FROM         dbo.Completion_Status LEFT OUTER JOIN
                      dbo.FDC_Trips ON dbo.Completion_Status.RUN_NUMBER = dbo.FDC_Trips.RunNumber CROSS JOIN
                      dbo.shiftlookupalacityfdpt
WHERE     (dbo.Completion_Status.CREATED BETWEEN dbo.shiftlookupalacityfdpt.start_dtm AND dbo.shiftlookupalacityfdpt.end_dtm) AND
                      (dbo.Completion_Status.Agency = 'Alameda City FD')
0
 
Anthony PerkinsCommented:
Just post the SQL Script in your image here http:#a38332714 as text and we can show you how to correct it.
0
 
Anthony PerkinsCommented:
Oops nevermind I see you just did it.
0
 
Anthony PerkinsCommented:
What is the name of the column that contains "M01_001234567"?
0
 
srodgers45Author Commented:
RUN_NUMBER
0
 
Anthony PerkinsCommented:
Then it looks like the solution posted here http:#a38333051 should do the trick.
0
 
srodgers45Author Commented:
EXCELLENT!!! Thank you very much for the help.
0
 
Anthony PerkinsCommented:
Wait a minute.  That was not me or the correct solution.  I will post a message for the moderators to re-open the question and allow you to select the correct comment.
0
 
srodgers45Author Commented:
Sorry, i guess i did not see someone else added a comment
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.