June 28, 2011 at 3:39 am
Hello,
Is there a way in T-SQL to select the number of characters/numeric values returned in a select statement.
For example, I have a table called 'product' it contains four 5 columns(vendor, Shipto, Weeks, Product and Amount). In the vendor column some of the results return a max of 7 numeric values is there a way to only select 5 instead
Current Results(7)
6473839
Desired Results(5)
64738
So what i would like is a select statement to achieve this, can anyone help me
Thanks in advance
--Product Table
CREATE TABLE [dbo].[Product](
[Vendor] [varchar](10) NULL,
[Shipto] [varchar](10) NULL,
[Weeks] [varchar](10) NULL,
[Product] [varchar](10) NULL,
[Amount] [int] NULL
)
-- Inserting Data
INSERT INTO dbo.Product
VALUES('454638','CA','1','Hard Drive',100)
INSERT INTO dbo.Product
VALUES('738372','SP','2','PC',64)
INSERT INTO dbo.Product
VALUES('4536373','EN','1','Laptop',30)
INSERT INTO dbo.Product
VALUES('6473839','GU','1','Cables',89)
INSERT INTO dbo.Product
VALUES('3635272','AF','4','Wires',20)
INSERT INTO dbo.Product
VALUES('0393837','JP','6','NAS',5)
INSERT INTO dbo.Product
VALUES('3637822','N','8','Router',19)
INSERT INTO dbo.Product
VALUES('8373632','USA','1','Printer',36)
INSERT INTO dbo.Product
VALUES('7636363','WA','10','Software',45)
INSERT INTO dbo.Product
VALUES('3636373','SC','9','WMware',4)
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 28, 2011 at 3:46 am
For character columns you can use LEFT function, and for numeric columns you can use / 100.
Is this what you mean?
-- Gianluca Sartori
June 28, 2011 at 4:00 am
Thanks for your reply I've tested the following code
select LEFT(P.Vendor,6)
, P.Shipto
, P.Weeks
, p.Product
, P.Amount
from Product P
The code above seems to do the trick wasn't familiar with this function. Are there anymore different mentioned to achieve the desired results.
You mention 'for numeric columns you can use / 100'. could you show me an example of this?
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 28, 2011 at 4:21 am
6473839 / 100 = 64738
-- Gianluca Sartori
June 28, 2011 at 4:27 am
is there a different mention instead of using /100 ????
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 28, 2011 at 4:39 am
You can use LEFT after casting to a character data type if that makes you feel better. Dividing by 100 does the exact same thing.
-- Gianluca Sartori
June 28, 2011 at 4:41 am
You have stated your problem and received a solution - if the solution is not what you require, please describe why, so that the next solution can give you what you need.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 28, 2011 at 5:02 am
OK sorry should of explained in more detail, the reason i asked for a different method for the numeric is because if i wanted to change the number of characters selected for example, 6 or maybe 8. Using 100 would not allow me to achieve this, so changing to any other number than 100 from my understand T-SQL would read this as product divided by 99.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 28, 2011 at 5:23 am
Then the CAST solution is probably the best for you, eg:
DECLARE @Int1 INT
SET @int1 = 454638
SELECT @Int1, LEFT(CAST(@int1 AS VARCHAR(20)),3)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 28, 2011 at 5:26 am
oh right that can work
Thanks for your help Gianluca Sartori and Phil much appreciated.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 28, 2011 at 5:42 am
June 28, 2011 at 5:51 am
use substring
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply