April 3, 2003 at 1:58 pm
Not sure if I am wording this correctly, but hope this will help:
I have a table with "Pallet_Name" and Pallet_Number.
I want to concat these to show "PALNAME-NUMBER".
HOWEVER, I want to get the pallet number to show as a (minimum) 5-digit number (I.E. PAL-00001). I have been able to get this working using a "LEFT('00000',5-len(PALLET_NUMBER))" EXCEPT if the original pallet number is GREATER than 5 digits to begin with ('123456').
Anything over 5 digits returns a error (left cannot take a "-" number).
If there a way to do the concat function and join it based on a IF selection (if len>5 then use number itself otherwise, do the LEFT(...) function?
I have not been able to find anything like this on the web, so any help would be greatly appreciated.
April 3, 2003 at 2:12 pm
May help a bit.
Declare @i int
Select @i=999999
Select Case When @i>99999
Then Right('0000'+Cast(@i as Varchar(10)),9)
Else 'PAL-'+Right('00000'+Cast(@i as Varchar(10)),5) End
April 3, 2003 at 3:04 pm
Is there any way you can increase the width of the resulting number from 5 to, say, 10? And the underlying column, of course.
SET @PalNumLen = LEN(Pallet_Number)
SET @NewPalNum = STUFF('0000000000', 10 - @PalNumLen + 1, @PalNumLen, Pallet_Number)
Or maybe:
SELECT <Other Columns>,
STUFF('0000000000',10-LEN(Pallet_Number)+1,LEN(Pallet_Number),Pallet_Number) AS 'Pallet_Number',
<More Columns>
FROM <table>
.
.
.
-SJT
Edited by - TheWildHun on 04/03/2003 3:05:43 PM
April 3, 2003 at 3:14 pm
Will this help
Declare @i int,
@wyd int
Select @i=999,@wyd=11
Select Right(Replicate('0',@wyd)+Cast(@i as varchar(20)),@wyd)
April 4, 2003 at 1:43 am
or
ISNULL(REPLICATE('0',5-LEN(PALLET_NUMBER)),'')+PALLET_NUMBER
Far away is close at hand in the images of elsewhere.
Anon.
April 4, 2003 at 11:24 am
I tried both replicate and stuff, but anything that was over the "5" or "10" digits would return a null value.
Basically, I want to see a minimum of a 5 digits number, but if the pallet_numberis GREATER than 5 digits, then just diplay it by itself.
I did try using a "if" to check the length and return a result based on the length, but (obviously) failed.
I.E. If len(Pallet_number>4) then Pallet_Number else Left('00000',5-Len(Pallet_Number))
I appreciate all the answers (new to SQL programming and never knew about the "replicate/Stuff" functions - I like those and have already used them in a couple other things!)
Just for kicks, here is the original SP I setup:
CREATE Procedure dbo.SP_RTV_DAILY
@DR datetime
AS
SELECT A.OBJID,
A.DATE_SCANNED,
A.SERIAL_NUMBER,
A.CAID_NUMBER,
A.CLAR_RECNUM,
C.USER_NAME AS SCANNED_BY,
D.RTV_LOCATION AS SCAN_LOCATION,
B.PALLET_NAME + '-'+CAST(B.Pallet_Number AS VARCHAR) AS PALLET_NAME,
E.COMP_NAME AS SHIP_TO
FROM dbo.RTV_MAIN_SERIALS A INNER JOIN
dbo.RTV_MAIN_PALLETS B ON A.PALLET_NUM = B.OBJID INNER JOIN
dbo.RTV_SEC_USERS C ON A.ENTERED_BY = C.OBJID INNER JOIN
dbo.RTV_DEFLT_LOCAL D ON C.USER_LOCATION = D.OBJID INNER JOIN
dbo.RTV_DATA_OEMS E ON B.SHIP_TO=E.OBJID
WHERE (A.DATE_SCANNED >@DR)
ORDER BYA.DATE_SCANNED DESC
GO
April 4, 2003 at 12:55 pm
Try this:
DECLARE
@Pallet_Name VARCHAR(10),
@Pallet_Num INT
SET @Pallet_Name = 'PAL'
SET @Pallet_Num = 12
SELECT
Pallet =
@Pallet_Name
+'-'+
CASE
WHEN LEN(@Pallet_Num) < 5
THEN RIGHT('00000' + CONVERT(VARCHAR,@Pallet_Num),5)
ELSE CONVERT(VARCHAR,@Pallet_Num)
END
-Dan
Edited by - dj_meier on 04/04/2003 12:55:32 PM
-Dan
April 7, 2003 at 4:25 am
Satech, look at David's answer
ISNULL(REPLICATE('0',5-LEN(PALLET_NUMBER)),'')+PALLET_NUMBER
it takes into account replicate with a negative length returns null. If PALLET_NUMBER can also be null then change to
ISNULL(ISNULL(REPLICATE('0',5-LEN(PALLET_NUMBER)),'') + PALLET_NUMBER,'00000')
to ensure you get 00000 as your return.
Edited by - antares686 on 04/07/2003 04:27:06 AM
April 7, 2003 at 2:40 pm
try to use the CASE statement
SELECT au_fname, au_lname,
CASE state
WHEN 'CA' THEN 'California'
WHEN 'KS' THEN 'Kansas'
WHEN 'TN' THEN 'Tennessee'
WHEN 'OR' THEN 'Oregon'
WHEN 'MI' THEN 'Michigan'
WHEN 'IN' THEN 'Indiana'
WHEN 'MD' THEN 'Maryland'
WHEN 'UT' THEN 'Utah'
END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname
The code for the case statement isn't as clean as that for the if statement. You might be able to write a custom function also
Greg Hannah
Greg Hannah
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply