June 29, 2011 at 4:27 am
Hi,
I have a columnn called Documentno which concatenate the vendor and shipto fields together.
The maximum number the vendor should be is 7 characters long for example 4536373
I want an if statement which looks at the number of characters(vendor) in the 'documentno' and if there is no vendor number than fill/pad with 4 spaces. However, if the vendor is included in the 'documentno' and depending on the number of characters include a so lets say 5 characters 45363 then fill/pad with 2 additional spaces to make the 7.
Expected Results with no vendor
PL01 _SC
Expected Results with 5 characters
PL01_45363 _SC
Can anyone help me write this alternations in my select statement or any suggestions please
Thanks in advance
--Create the DB
CREATE TABLE [dbo].[Product](
[Vendor] [varchar](10) NULL,
[Shipto] [varchar](10) NULL,
[Weeks] [varchar](10) NULL,
[Product] [varchar](10) NULL,
[Amount] [int] NULL
)
-- Insert data into the table
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('','USA','1','Printer',36)
INSERT INTO dbo.Product
VALUES('','WA','10','Software',45)
INSERT INTO dbo.Product
VALUES('','SC','9','WMware',4)
-- My script so far
select 'PL01'+'_'+vendor +'_'+shipto as DocumentNo
from Product
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 29, 2011 at 4:53 am
Cast to a fixed length character type:
select 'PL01'+'_'+ CAST(vendor as char(7)) +'_'+shipto as DocumentNo
from Product
Hope this helps
Gianluca
EDIT: fixed typo
-- Gianluca Sartori
June 29, 2011 at 5:58 am
Thanks for your reply Gianluca Sartori
Its not quite but close to the desired results that I'm expecting.
For example if i change 7 to lets say 3, then only 4 characters are shown in the documentno where as i want to count the number of characters and if its 5 then add 2 spaces which totals to 7 or if there is 7 add no additional spaces.
select 'PL01'+'_'+ CAST(vendor as char(3)) +'_'+shipto as DocumentNo
from Product
'Results not right'
DocumentNo
PL01_4546_CA
PL01_7383_SP
PL01_4536_EN
PL01_6473_GU
PL01_3635_AF
PL01_0393_JP
PL01_3637_N
PL01_ _USA
PL01_ _WA
PL01_ _SC
when i try to think about the logic of it the only thing that comes to mind is the IF Statement, but i don't have a clue how to figure out how to only count the vendor and include the additional spaces
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 29, 2011 at 6:11 am
I'm sorry, I don't understand. Can you please post the expected results based on your sample data?
-- Gianluca Sartori
June 29, 2011 at 7:10 am
Steve, working effectively with TSQL, as with any programming language, requires experimentation...
SELECT 'PL01' + '_' + CASE
WHEN vendor IS NULL THEN SPACE(4)
WHEN LEN(vendor) < 4 THEN CAST(Vendor AS CHAR(4))
WHEN LEN(vendor) = 5 THEN CAST(Vendor AS CHAR(7))
WHEN LEN(vendor) = 7 THEN CAST(Vendor AS CHAR(7))
ELSE Vendor END
+ '_' + shipto as DocumentNo
FROM Product
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 29, 2011 at 7:17 am
[] = Represent spaces
So the vendor can only contain a maximum of 7 numeric values i want a statement which counts the number of numeric values of the vendor lets say there's 6, 454638. As the vendor needs to have a maximum of 7 numeric values I would expect in the results a space. For example, 454638[] <---- as shown you can see that a space is included
so my expected results would be as followed
PL01_454638[]_CA
PL01_738372[]_SP
PL01_4536373_EN
PL01_6473839_GU
PL01_3635272_AF
PL01_0393837_JP
PL01_3637822_N
PL01[][][][][][][]__USA
PL01[][][][][][][]__WA
PL01[][][][][][][]__SC
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 29, 2011 at 7:31 am
Jnrstevej (6/29/2011)
...So the vendor can only contain a maximum of 7 numeric values i want a statement which counts the number of numeric values of the vendor lets say there's 6, 454638. As the vendor needs to have a maximum of 7 numeric values I would expect in the results a space. ...
So, if the vendor is NULL or empty, you want to replace it with four spaces, otherwise you want to pad right to 7 spaces?
Edit:
Which would be pretty much what Gianluca posted...
select 'PL01' + '_' + CASE
WHEN NULLIF(vendor,'') IS NULL THEN SPACE(4)
ELSE CAST(vendor as char(7))
END + '_' + shipto as DocumentNo
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 29, 2011 at 7:40 am
ChrisM@Work just read your post that is the absolutely spot on , I didn't think a CASE Statement could achieve what i was looking for so i quickly dismissed it for the IF Statement.
I've played around with the code and understand what i need to get my desired results thanks a lot for your help the results look right and i learnt someone new 🙂
SELECT 'PL01' + '_' + CASE
WHEN vendor IS NULL THEN SPACE(4)
WHEN LEN(vendor) < 4 THEN CAST(Vendor AS CHAR(4))
WHEN LEN(vendor) = 5 THEN CAST(Vendor AS CHAR(7))
WHEN LEN(Vendor) = 6 Then CAST(vendor AS CHAR(7))
-- WHEN LEN(vendor) = 7 THEN CAST(Vendor AS CHAR(7))
ELSE Vendor END
+ '_' + shipto as DocumentNo
FROM Product
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 29, 2011 at 7:44 am
Jnrstevej (6/29/2011)
... I didn't think a CASE Statement could achieve what i was looking for so i quickly dismissed it for the IF Statement....
CASE works within a statement, IF chooses statements.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 29, 2011 at 2:29 pm
I may be missing something here, but why would you pad a numeric value with spaces at the end? I would think a better format would be to pre-pend a 0 for the numeric values - at least that is the normal way I've seen it done.
1234 becomes 0001234
12345 becomes 0012345
123456 becomes 0123456
If no vendor, I would recommend returning all 0's.
This will make sure your computed column is always aligned and will also sort appropriately (if needed).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2011 at 3:16 am
I completely agree with you Jeffrey unfortunately that was the requirement and didn't want it no other way only spaces.
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply