Hi Rui,

The result for Sample Scripts seems doesn't produce the sample data. it seems missing a space for each of the first row .

there is also a missing underscore in line 30, to form the digit "4"

Thanks.

No, the missing underscore character is there for a purpose. Compare the source data and the expected output.

Can we write a function or stored procedure? or even it should be through one TSQL statement?

Hello Rui, Jacob,

9 digits is a fixed value or should we take care of other digits length ?

Does each line have exactly the length of 3 * [digits length] or could it have some trailing spaces before the carriage return ?

In the sample data, with copy/paste, the last id does not produce a full spaces last line, is this the behavior or does this come from the copy/paste ?

Regards,

Aurelien

The rules are chaning again?!

I give up ^^

Challenge 15 went to interresting to trivial.

And this one goes from interresting to tricky at the moment being but who knows how trivial/tricky it'll end.

BTW: a "Delete" button would do good in the media gallery.

Hi Jacob,

Still it seems there is missing one space at end of each digit. As you mentioned Each digit is represented using 9 cells (3x3), but if you can see below example its not.

eg.

' _  _  _  _  _  _  _  _  _' ----> 1 space is missing

'| || || || || |  || ||_ |_|'

'|_||_||_||_||_|  ||_| _| _|'

'    _  _  _  _  _  _     _' ----> 1 space is missing

'|_||_|| || ||_   |  |  ||_' ----> 1 space is missing

'  | _||_||_||_|  |  |  | _|'

-- Pragnesh

Jacob,

Continue in above example...

1st 3 lines are from ID = 1

and next 3 lines are from ID = 2

--Pragnegsh

Jacob,

When I execute the data loading script I end up with 6 rows with the first 5 lines each of length 118 and the last line of length 91. The first 5 rows also contain 27 trailing blanks. All rows end with a final cr/lf.

So are we dealing with 6 rows or 18 rows?

Oh yes, and there a 3 cr/lf in each row separating the 3 blocks of characters.

@jacob,

Thanks for all your answers.

Another question now regarding leading and trailing spaces !

Should we trim them or should we detect a X when a 9x9 spaces square is detected ?

Example:

 _     _ 

| |   | |

|_|   |_| => 0X0

What about:

    _    

   | |   

   |_|    => X0X or 0 ?

(I hope formating will be ok!)

#1 - Your code should take care of more than 9 digits.

 - This is not a requirement set out in the question.  Based purely on the rules in the question, I think any solution looking for more than 9 digits would be over-engineered.  If extra digits exist, then I would expect any solution to simply ignore them.  Is this a fair assessment?

Also, when I copied the code to produce the test table, I only received line feed characters at the end of each row, not the cr/lf combination.  Thought that was worth pointing out.

Jacob,

OK, so you are confirming 6 rows in the sample data broken up by cr/lf all ending with 27 blanks followed by a cr/lf. Totale length each 116 or (27+2)x4.

Earlier you responded to Aurelien sayinging that 'your code should take care of more than 9 digits'. I submit that this is impossible to do in a dynamic way. The solution must somehow be told, at least via a parameter, how many digits to expect.

After all, if you start throwing garbage at the solution with the data rows of different lengths there is no way for the solution to know whether this is an error without also knowing how many digits to expect.

I'm happy with the requirements now, although it does mean I'll have to change my solution...

Can we check only single cr (as first char of delimiter) instead of cr/lf pair?

Jacob,

 Is any extra space is there in for every top row of each record for the sample data???

Since, when i copy the data as it is and run against the query, its not matching any record... when i delete one extra space at the top row of every record then the numbers are matching and the query giving output.....

[quote]

3. The number of 3x3 blocks in each record may vary. Not all records will have 9 digits. It is also possible that some records will have more than nine 3x3 blocks.

[/quote]

This seems to contradict one of the original stated rules: Each account number is 9 digits long.

If the account number is going to have a variable number of digits, can you give us a range (eg. 0-20 digits)?

Can you update the article with the rule clarifications in the comments. It is getting difficult to understand the problem as stated and then read through user comments to pick up changes to the original rules.

For the performance part of the challenge, you should specify an upper limit for the number of digits (as suggested by whitneje) or you should specify that there is 'essentially' no upper limit. The approach that needs to be taken with one rather than the other can have a notable impact on performance.

to all,

We paid a lot of attention to publishing problemns due to printing plain text in html but sometimes mistakes should appear when you try to download the sources.

In all the cases I suggest to use the provided function of the syntax highlighter to copy/paste the content (mouse over on the right top corner of the displayed source, then copy to clipboard). I just check it and it is correct right now.

As dishdy said the format of the scanned digits should have a length of 116 -> (27+2)x4. Each cell 3x3 should be complete with its exact size and you should not trim spaces.

For the size of the scanned number it should fit exactly 9 cells digits but your code should handle any number of digits. But that's more an information on how you should produce your query than a requirement! If your query produce exactly the values in output provided with the provided sample data it will  be valid!

Again, I see sometimes this remark, the printed output represend the values expected you are not supposed to make a query to produce characted by character the printed output. This is only a textual representation of the table data expected .It is easier for publishing reasons to set output in Management studio to text then make a copy/paste for edition than set the output to standard table and make a screen capture of the results. If this is a trouble point we can change this in our future challenges.

As Jacob said we should also provide in the future early access to the challenges for a few of you as reviewers (2-3 different people for each challenge), contact us if you are interested.

Cheers!

;-)

Is the output required to be sorted by ID?

@whitneje,

yes even if it was not explicitely mentionned you should sort it by ID as in the expected output.

I still think for the performance part of this challenge you should give us un upper limit for the number of digits your code should handle.

Or simply stated, can we assume that ScanNumber is stored in a varchar(n) column and not a varchar(max) column?

Thus we can assume n to be at most 8000 and take it from there? Meaning that we need to deal with at most (8000-8)/4 or 1998 digits?

On the other hand, if n=max then we need to potentially deal with over 500 million digits.

Jacob,

I'm new to this TSQL challenge thing. Thus each solution will go through the basic, logic and load testing. We have the data for the basic testing but you keep secret the data you use for the logic and load testing. The winner has to pass the first two tests and be first in the load testing.

I presume that it's in the logic testing that you will have variable length rows plus at least one row with a huge number of digits. And if it can't handle it I'm out. Is that correct?

My I suggest the following for the tricky data. A string containing just 'ABRACADABRA'. An empty string. That should get heads rolling.

Naturally, I'm joking. :-))

"There is no upper limit for the number of characters in the string. Your solution should not assume a fixed number of characters in the string. "

Well in the example provided, the scannumber is Nvarchar(116)..making thus 116 chars in the string the maximum.

Following this the maximum number of "digits" that can be placed in the string on a 3x3 basis is 12

ie ( (116 / 3 ) - 2 ) / 3  = 12

This could be then relevant in terms of neat formatting of end result.

karinloos,

From what I have understood so far, I think you should expect the logic test data that will be applied to your solution to NOT be constraint by a mear nvarchar(116) column. Thus your solution should handle at least a nvarchar(4000) column. In fact, there is no need to use nvarchar for representing this data. Thus you might get hit with a varchar(8000) column or even a varchar(max) column. In the latter case, you need to deal with a number with a potential 500 million digits.

Maybe this challenge is trying simply to solicit a solution based on a VARIABLE number of digits without the need to really support 500 million digits implied by a varchar(max) column.

Until this is made clear, the load testing part will be absolutely meaningless.

Should ScanNumber = char(10)+char(13)+char(10)+char(13)+char(10)+char(13) (zero 3x3 digits (zero is divisible by 3)) result in 'X' or '' or NULL?  

We should suppose that scanner will produce errors sometimes but always by putting a '|','_',' ' in the wrong place. In the case of a problem where no character is provided it will move the characters to left or right depending on the missing characters! then in this case, your query should focus on the 3x3 cells, if a character is missing it will produce the wrong digit and you put X. If the scan is really empty with only the CRLF, there is no digit, so '' or NULL is ok for me. That's not a tricky point.

Hope it helps

Does the script which produces the following results calculates code length correctly? (it's based on the length of the substring between 1st and 2nd CR)

DECLARE @t TABLE (Id int, ScanNumber NVARCHAR(216))

INSERT INTO @t

SELECT 7, --> 012 345 678 X90 867 716

'                               _     _  _     _  _  _  _     _  _  _  _  _  _     _

| |  | _| _||_||_ |_   ||_|| ||_|| ||_||_   |  |  ||_

|_|  ||_  _|  | _||_|  ||_|  | _||_||_||_|  |  |  ||_|

'

UNION SELECT 8,

'_

'

UNION SELECT 9,

' '

UNION SELECT 10,

' _     _  _     _  _  _  _     _  _  _  _  _  _     _

| |  | _| _||_||_ |_   ||_|| ||_|| ||_||_   |  |  ||_

|_|  ||_  _ ||_|  | _||_||_||

';

Results:

id number

-- ----------------------

7 012345678X90867716

8 NULL

9 NULL

10 012XXXXXXXXXXXXXXX

Serg54,

I think you must assume the presence of at least one cr/lf. If not you should return an empty or null string. This is also true if the first cr/lf is found at position one.

Once you know the location of the first cr/lf you can assume the location of the second cr/lf even if it is not there. From this, everything else can be determined.

Sebastian,

"2. The length of the first three lines of each recrd will always be the same and will be divisible by three."

" #2 - There may be spaces (leading or trailing) in the string values."

Do you mean the spaces (leading or trailing) are never added to the lines, but only can substitute some symbols not changing the line lehgth?

These SQL Challenges are starting to look more like hopeless pursuits than reasonable challenges, and I'm going to not bother participating in any more of them until the authors realize how simple it is to eliminate 99% of the repeated problems by doing the simplest of things - namely: 1) provide a link to download the sample data SQL file, and 2) TEST the sample data and SQL file to ensure that selecting from it's table produces the correct result.

The more serious problem would still remain, however, and that's the fact that the authors just don't realize how thorough they need to be in constructing these challenges.   There should never EVER be any questions of the nature of those being repeatedly asked on these things.   The guidelines should NOT be on a separate page, but included in every challenge page, or perhaps available in a pop-up window, and easily printed.   I don't mind doing the work to solve the problems offered, but you as authors have to at least take the responsibility of ensuring there can be no unanswered questions, and that probably means that these problems need to be "vetted" through someone who's primary language is English, and is the kind of person who can be anally thorough in ensuring the problems are properly constructed.

I was already a good hour into working on this when I then discovered that once again, the rules had, for all practical purposes, changed completely, and I was going entirely in the wrong direction.

Unfortunately, the comments to date have yet to provide sufficient detail as to how to parse the digit data, as there's a statement indicating the possibility of leading and/or trailing spaces, but without saying whether each digits data or only the entire line of digit data is what that applies to, and if it's not just the entire line that this applies to, then this problem is pointless, regardless of whether it's solvable or not.  No person with 2 ounces of common sense would make the attempt to get this data if it were so inconsistent as to have possible leading or trailing spaces for every digit, and also the possibility of no intervening spaces, as then there'd be no way to guarantee that you could accurately parse the digits, as there'd be no way to determine where a given digit starts and finishes.   Yes, these are the kinds of issues associated with unstructured data, but some of them CANNOT be solved, and based on what's been said so far, it's not clear to me that your problem is in the category of being able to be solved.

Come to think of it, I'm not even sure you can reasonably allow leading spaces on the line as well as trailing spaces, because then you need a strict rule that says exactly where to begin looking at characters because 3 spaces is considered a valid

piece of data and needs to be represented in the output as a space.   Admittedly, you'd need 3 lines of it, but when parsing one line, you'd need to know if the other lines for that same digit are also 3 spaces in the same position, and at that point, dealing with unstructured data is a problem that's at the very least, extremely difficult for the most skilled string manipulators, who would no doubt opt for better data validation rather than invest in continuing down a road where the most likely outcome is reaching the conclusion that parsing the data is actually impossible.  I've been parsing unstructured string data for the last 30 years, so please give this problem a LOT more thought than it's been given.  I think you'd be better off withdrawing this one and re-creating it from scratch, but that's just me.

sgmunson,

As far as I'm concerned, this problem is very clear now. Yes it took a bit of hackling backwards and forwards and some of the specs are now in the comments.

Why should the presence of spaces (leading or trailing) be confusing? A space is just like a digit.

In general, give me one sample line of data that you think has an ambiguous answer given the current specs.

Regards.

Now that I've had some time to re-analyze, I've concluded you have zero chance at a solution if you allow both leading and traling spaces and no specific number of digits to be found.  There is no way you can find the starting point for all possible inputs under those conditions.

You're going to have to specify some very exacting conditions or have no solution.  Just one leading AND one traling space, with either or both being optional, destroys your chance at the problem being solved.   Think it through, folks.  This one is dead as a doornail.   There's no way you can consider 3 consecutive spaces a valid part of the digit 1 and also allow for leading and trailing spaces, and for all inputs, have a valid solution, even if the number of digits is specified, and the scanner be considered "less than 100%".

If you want to prove otherwise, you'll have quite a task ahead of you, as you'll have to generate ALL POSSIBLE INPUTS, including all possible complete 100% screwups by the scanner (pretend it can be broken in all possible ways, including absolutely no consistency in which text elements it outputs based on its input.

Ok, now I see where my problem with this began.  A re-read of the original problem guarantees no leading or trailing spaces in the digit data, yet that became a significant concern within the comments, that I misunderstood - the wording used was vague, and my interpretation wasn't helping.  I'm not even going to try to re-interpret, as that has nothing to do with the challenge solution.

My apologies for any ruffled feathers.  I see now what needs to be done.

@sgmunson,

I am happy that you are now in touch with the challenge ;-)

about your first comment, please note that we are a small team of volunteers trying to do their best to provide great challenges, but sometimes things that seem clear for us are not finally so obvious and some mistakes can also appear.

That's why our team is growing and that's why we purpose in the last newsletter to people to get an early acces to challenges in order to beta test it.

hope it is clearer now and that you will enjoy challenges!

I am very interested in your story about parsing unstructured data ;-)

Very good challenge this time!

I cannot se anything in the status about tally tables. Can we use the taly table your defined in an earlier challenge, tsqlc_Tally, or must we make our own?

kongstad126,

Looking at some of the solutions to past challenges (very interesting for a new-comer to this challenge site like me), it appears that table master.dbo.spt_values for type='P' is used for a tally table. Thus I presume it can be used for this challenge.

But the size of your tally table will probably impact the performance of your solution and it will probably also impact the actual maximum number of digits your solution can handle. The specifications say that there should be no upper limit on the number of digits your solution can handle. For this to be true, your solution should handle a varchar(max) column containing more or less 500 million digits. And I presume their logic testing data will have a column containing 500 million digits.

Sebastian,

Thus can you please confirm or clarify this issue of 'upper limit' for the number of digits.

@kongstad126, @dishdy

yes, you can use spt_values or an inline query or anything else you need for you query if it fit the rules ;-)

About the upper limit for a load testing, remember some few points:

- first of all, we test the logic, before thinking about load testing ensure you have a solution that works with the sample data.

- then you should try your solution with a significant rumber of rows. This should be from 1000 to 10000. It is not enough for a very hard load testing but it is quite relevant to check the bad solutions and see differences between them.

- if you can do a high load testing with 1M rows, this  is far enough. Maybe we go ahead, but its kept secret ;-)

Hope it helps

Rui,

If the approach you use is a tally table to handle a variable number of digits then you necessarily need to place an upper limit on the number of rows this tally table holds. Thus if you use a tally table with 2048 rows (master.dbo.spt_values for type='P') then your solution will handle at most 2048 digits. But if your logic test data has a row with 2049 digits, you're out.

In practice, you can always increase the number of rows of the tally table by cross joining it with itself as many times as is necessary. But increasing the number of rows of the tally table to handle some unknown maximum will impact the performance. If you set your tally table to 1 million rows and your logic test data has at most 100 digits then you're wasting a lot of cpu. That's why I think the specification should indicate a maximum.

On the other hand, if you guys have a solution which does not make use of a tally table and rather makes use of some technique (e.g. recursive CTE or something very clever) which is truly dynamic then my request for specifying an upper limit is not valid.

Please note, this is has nothing to do with the number of rows your load testing data will have.

I agree with the interest of having an upper limit of digits (and I consider it actually implied within the rules as they are stated).

I used a limit of 64 digits and (as commented in my query code) : I hope it will be enough.

You can't have more than 12 "digits" in a NVARCHAR(116), anyway.

Sergejack,

This is true for the sample data. I don't know if you can assume their logic test data and load test data to have ScanNumber defined as nvarchar(116). But if this is the case, then your solution needs to handle a variable number of digits but it can assume a maximum of 12 digits.

My solution using a tally table eats 10000 rows with 1000 numbers in no time.

Using only recursive CTE's the same query is slow (I don't know how slow, I am impatient, but more than 10 minutes anyway, which means I suck at doing recursive CTE's, or just that I should test my code on something else than a virtualbox virtual machine running on a ubuntu laptop ;)

One way to make sure my solution is not canned bacause my tally table is to small is to submit multiple solutions. One each with a tally table of perhaps 1000, 10.000, 1.000.0000.

This ensures that my solution can be evaluated on the largest set of digits they through at it, but still with the best performance I can get with a tally table.

It does seem a waste of my time, and more important a wate of the judges time.

Its just that it given the description of the challenge it would seem silly to go so far given that we are supposed to be scanning customer numbers, any test with more than 20 digits would be silly, and a million digits is just stupid. I can see the scanner messing up the format, but not suddenly multiplying the number of digits by several orders of magnitude.

So all in all I will join the choir of voices asking for an upper limit on the number of digits, if not 20 then perhaps 1000 should be more than enough to test the wits of us all?

Sebastian,

Well done and said! Now that we all understand that the 'no upper limit' thing is part of the challenge, the solution jumps right at me. I think the whining of the upper-limit choir will now stop.

I submitted a solution that starts with "WITH", but not ";WITH"...I assume that this will be ok...since i either had to break the "a single query that starts with "WITH" or "SELECT" rule or risk the query throwing an error if you didnt add and ";" to the sample data.  I  know this is being nitpicky, but i also assume that you will have many submissions and will automate the validation, and one such validation might have been checking that the first character is a "W" or "S"...

Hi Jacob,

I have one question regarding how you compare the result. For an example,

Id          Value

----------- ---------

1           000007059

2           490067715

3           680X68279

4           490867716

5           X90867716

6           012345678

Id          Value

----------- ---------

2           490067715

1           000007059

3           680X68279

4           490867716

5           X90867716

6           012345678

both are valid results? If NOT then please add in the rule to sort the result by Id column.

Hi Parth,

If you have the right value associated with the right id, and as we didn't explicitely specified an order rule, it should pass the logic test.

But usually when we said "this is the expected output", even if there is no explicit rule, keep in mind that we hope people give solutions to fit exactly the values and orders. And most of the time in real life, you don't have complete specifications and only 'this is the result I want to get'...

So, consider that it should fit in logic test but may have a lower note that the same solution with equal performance (as soon as we can consider that doing the final order to show the results as expected is a difficulty).

Hope it helps

Thanks Rui. I understood your point.