May 6, 2016 at 2:09 pm
Hello,
I'll start with my sample table data.
--Populating sample table data
CREATE TABLE
#OrderInfo
(
OrderInfoPk INT,
OrderNumber VARCHAR(10),
PurchaseDate DATE,
CustomerNumber VARCHAR(6)
)
INSERT INTO #OrderInfo (OrderInfoPk, OrderNumber, PurchaseDate, CustomerNumber) VALUES (1, 'ORD00123','1/2/2012','AMG2')
INSERT INTO #OrderInfo (OrderInfoPk, OrderNumber, PurchaseDate, CustomerNumber) VALUES (2, 'ORD001234','11/18/2014','CRM114')
INSERT INTO #OrderInfo (OrderInfoPk, OrderNumber, PurchaseDate, CustomerNumber) VALUES (3, 'ORD0012345','3/25/2012','VXT10')
INSERT INTO #OrderInfo (OrderInfoPk, OrderNumber, PurchaseDate, CustomerNumber) VALUES (4, 'ORD0012','12/9/2012','HO922')
--Viewing the data
SELECT * FROM #OrderInfo
/*
Viewing the data with the padded strings I'm expecting to see:
1) 12 characters for the OrderNumber with preceding zeroes for padding
2) 6 characters for the PurchaseDate in MMDDYYYY format
3) 8 characters for the CustomerNumber with preceding zeroes for padding
4) ...for a total of 28 characters
*/
SELECT OrderInfoPk = 1, PaddedString = '0000ORD00123010220120000AMG2'
SELECT OrderInfoPk = 2, PaddedString = '000ORD0012341118201400CRM114'
SELECT OrderInfoPk = 3, PaddedString = '00ORD001234503252012000VXT10'
SELECT OrderInfoPk = 4, PaddedString = '00000ORD001212092012000HO922'
--Dropping the table
DROP TABLE #OrderInfo
I'm trying to figure out how I can retrieve a string that concatenates the OrderNumber, PurchaseDate, and CustomerNumber into one string. The total number of characters in the string needs to be 28, and each of the fields in the string requires a specific number of characters, which I break down in the query above.
I would greatly appreciate any assistance!
May 6, 2016 at 2:23 pm
This is the sort of thing I would avoid doing at the database layer if possible, but if it must be done, here's one way (kudos on the sample data, by the way!):
SELECT OrderInfoPk,
PaddedString=RIGHT('000000000000'+RTRIM(OrderNumber),12)+REPLACE(CONVERT(char(10),PurchaseDate,101),'/','')+RIGHT('00000000'+RTRIM(CustomerNumber),8)
FROM #OrderInfo;
Cheers!
May 6, 2016 at 2:29 pm
tarr94 (5/6/2016)
Hello,I'll start with my sample table data.
...
I'm trying to figure out how I can retrieve a string that concatenates the OrderNumber, PurchaseDate, and CustomerNumber into one string. The total number of characters in the string needs to be 28, and each of the fields in the string requires a specific number of characters, which I break down in the query above.
I would greatly appreciate any assistance!
Note that the second, third and fourth examples have 29 characters. Assuming you meant for them to have 28 characters and you meant 8 characters for the PurchaseDate in MMDDYYYY format, you can try this:
SELECT OrderInfoPk,
right(replicate('0', 12) + OrderNumber, 12) +
replace(convert(char(10), PurchaseDate, 110), '-', '') +
right(replicate('0', 8) + CustomerNumber, 8)
FROM #OrderInfo
Don Simpson
May 6, 2016 at 2:31 pm
Jacob Wilkins (5/6/2016)
This is the sort of thing I would avoid doing at the database layer if possible
+1
Definitely agree.
Don Simpson
May 6, 2016 at 2:37 pm
Thank you for the responses! I'll give this a try. Looks like the solution might have been simpler than I had expected.
I've updated my original post to fix the PaddedString values. Not sure how I missed that.
Two of you have chimed in with a preference for keeping this sort of logic out of SQL. Could you please clarify why you feel this way? In my example, the query results are being pulled into a Crystal Report, so the only other alternative is to keep this logic in formula fields in Crystal (which is what we've been doing up to this point, but we're trying to move away from keeping this kind of logic in Crystal formulas). I'll mark Jacob's answer as the solution since he chimed in first and his query seems to do the trick, but I'd be interested in any opinions on whether the logic should be stored in SQL or Crystal.
May 6, 2016 at 3:02 pm
tarr94 (5/6/2016)
Thank you for the responses! I'll give this a try. Looks like the solution might have been simpler than I had expected.I've updated my original post to fix the PaddedString values. Not sure how I missed that.
Two of you have chimed in with a preference for keeping this sort of logic out of SQL. Could you please clarify why you feel this way? In my example, the query results are being pulled into a Crystal Report, so the only other alternative is to keep this logic in formula fields in Crystal (which is what we've been doing up to this point, but we're trying to move away from keeping this kind of logic in Crystal formulas). I'll mark Jacob's answer as the solution since he chimed in first and his query seems to do the trick, but I'd be interested in any opinions on whether the logic should be stored in SQL or Crystal.
For me it's just that as a matter of general practice, I prefer to keep matters of presentation format out of the database layer.
For one thing, the database engine is often not particularly efficient at formatting strings; software designed to be used as a presentation layer typically handles presentation tasks better than the database engine.
For a second thing, as you start presenting data to more and more client applications, if you've slipped into the habit of handling that at the database layer, that's a bunch of extra work that could be spread out across the various clients, but is instead being forced onto software that's not particularly good at it to begin with.
There's a decent bit of material about this floating around the internet, so I won't go into much more detail here.
Here are a couple similar sorts of explanations with more details that could get you started on your own investigation of the topic:
http://stackoverflow.com/questions/929318/should-sql-format-the-output-or-just-retreive-the-raw-data
In an isolated case as simple as this one, if it truly is a one-off sort of thing, then you'll probably be fine. It's just one of those things that tends to creep up on you over time, and the end result is usually not a happy one.
Cheers!
May 6, 2016 at 5:24 pm
tarr94 (5/6/2016)
Thank you for the responses! I'll give this a try. Looks like the solution might have been simpler than I had expected.I've updated my original post to fix the PaddedString values. Not sure how I missed that.
Two of you have chimed in with a preference for keeping this sort of logic out of SQL. Could you please clarify why you feel this way? In my example, the query results are being pulled into a Crystal Report, so the only other alternative is to keep this logic in formula fields in Crystal (which is what we've been doing up to this point, but we're trying to move away from keeping this kind of logic in Crystal formulas). I'll mark Jacob's answer as the solution since he chimed in first and his query seems to do the trick, but I'd be interested in any opinions on whether the logic should be stored in SQL or Crystal.
A simple example of a potential problem is the need for one of the pieces (such as order number) to be displayed by itself on another part of the report/form. If you build the string in sql, then you either need to bring back order number by itself in an additional column, or build some string splitter logic in the report.
Why are you trying to move away from keeping this kind of logic in Crystal formulas? That's what the reporting tool is really good at.
Don Simpson
May 9, 2016 at 8:38 am
Don,
I think future maintainability is the main concern. We have several Crystal reports driving off of the same stored procedure and all of them require the same padded string. If changes are needed for the padded string, we can update them in one place.
The team I work with has also shown a preference for keeping things as data driven and SQL oriented as possible for maintainability's sake, but it sounds like this isn't always the best plan from a performance standpoint. Maybe we need to revisit that as a team.
May 9, 2016 at 4:50 pm
tarr94 (5/6/2016)
Two of you have chimed in with a preference for keeping this sort of logic out of SQL. Could you please clarify why you feel this way? In my example, the query results are being pulled into a Crystal Report, so the only other alternative is to keep this logic in formula fields in Crystal (which is what we've been doing up to this point, but we're trying to move away from keeping this kind of logic in Crystal formulas). I'll mark Jacob's answer as the solution since he chimed in first and his query seems to do the trick, but I'd be interested in any opinions on whether the logic should be stored in SQL or Crystal.
The big reason is "layer separation" and all of the good things that go with it. While there are certainly exceptions, it's normally a good idea to keep the Presentation Layer separate from the Data Layer. It's especially important for date/times and currency so that local formatting can take place on the client side of the house.
Shifting gears, I find leading zeroes to be even more annoying than leading spaces. Again, there are exceptions that require them but I'm not a fan even then.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply