Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

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!!
0
srodgers45
Asked:
srodgers45
  • 8
  • 6
  • 5
1 Solution
 
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
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now