sorting an alphanumeric field
-
After looking over the expected output, I think I want to retract the fact that I'd bet you are correct. It's still possible, but I don't think I consider it likely anymore. If we assume that the first character is the "parent" value, such as an assembly, and that the second character is the "child" value, such as a subassembly, I'd not expect him to want 10 and 22 to come after 7 in the desired output, and there would also be a pretty low limit on the numbers of parents and children you can have. On the other hand, if we say that the "parent" value isn't necessarily limited to a single character, I'd have expected some of the sample data to be 3 or more characters long, such as 10A, etc.
Either way, if they are a concatenation of anything, and assuming that we at least have lookup tables to work with, I'd probably just sort on parent, then child, joining to lookup tables if the concatenated columns aren't available in the source table. Seems a lot easier and faster.
-
> Until the OP returns and tells us their purpose, it is impossible to be sure that your conclusion, which again is likely the case, is in fact correct.
True.
As well as it's impossible to say if Peter's solution is actually valid.
He built it based on small extraction from actual data, not on the logic it must follow.
Because the logic was never revealed.
Despite all applauds to OP.
So, nobody knows if there are (or there will be next week) some values which don't follow the rules Peter invented based on the posted example.
The only thing is clear from OP: in order to provide required sorting data must be normalized. Even Peter agreed with it.
But which way - we don't know.
There are no business rules specified.
_____________
Code for TallyGenerator -
I think we scared the OP away...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I've tried three times now to write this, so this will just have to do - this was an irritating thread to read. We're here to share knowledge and solve problems. Nothing wrong with trying to show someone that a 'better' technique would help them in the long run, but sometimes we'll just have to help solve ugly problems in ugly ways, and do it with grace!
-
As a long-time consultant, I completely agree.
If a client has an existing system that is poorly designed, but that they are happy with, our job is to work around it, not refuse to lift a finger until they change it to our satisfaction. There is nothing wrong with explaining why it would be better to do things differently, but that doesn't change the fact that we often are required to "dance with the one what brung ya." Or, as I said previously in a similar thread...
Joanie, don't let him get to you. While in fairness, <insert unnamed postername here> is often (but not always) correct in his assessments, he also apparently lives in a fantasyland where everyone is hired by a company that is just beginning to use SQL Server, and therefore has full and utter control over the design, where no one is a short term consultant that has to work within the existing framework no matter how badly designed, and where there aren't a million applications already in existence that are running against a database, thus making design changes extremely tricky. He also seems to forget that there is a state in the learning process that is between "I don't have a freaking clue about SQL Server" and "I'm a a SQL Server god". In reality, the vast majority of SQL folks are at some point between the two, and helping them towards the latter goal is why this board is here.
While it's always a good idea to attempt to fix problems with design rather than to work around them, some of us in the real world are perfectly willing to let you know what would help if you do have the option of fixing things, while at the same time helping you solve your immediate problem if you don't. Most of us have been in both pairs of shoes.
-
Bill presented a business problem, and Peter's first post worked perfectly according to the definition of the problem. The thread could have ended there.
System design and normalization was never within the scope of the original problem. It was inappropriate to accuse the data of being poorly designed; the data is just the data.
-
WOW! Don't look over the weekend and I see a firestorm of replies. Thanks all who replied. William Mitchell was the closest for what the data represents. We have engineering drawings that are sorting key numbers represented as alpha numeric data in a VARCHAR. I cannot normalize the data as it designated the way it is, no particular standard. It could be numeric on some keynumbers, it could be a mix (11, 11A, 11B, 12) and it can even be alpha leading (S1, S2, etc.).
So, what did I use to resolve the issue? Well, I got the following suggestion and went with this:
SELECT DiagramModelID, DiagramID, DiagramKeyNumber,DiagramPartNumber, DiagramPartDescription, Status,
SequenceNumber
FROM [dbo].PartsDiagramModel
WHERE DiagramID = @DiagramID
AND Status <> 3
ORDER BY
-- if the key number starts with a non numeric first character then just order by keynumber
-- else sort the alpha numeric key number
CASE WHEN ISNUMERIC(DiagramKeyNumber) = 1 THEN CAST(DiagramKeyNumber AS INT) when
patindex('%[A-Z]%',DiagramKeyNumber) > 1 then
convert(int,substring(DiagramKeyNumber,1,patindex('%[A-Z]%',DiagramKeyNumber)-1)) else 999999 END ASC,
CASE WHEN ISNUMERIC(DiagramKeyNumber) = 0 THEN DiagramKeyNumber END ASC
StringValue in my original test case was equivalent to DiagramKeyNumber. I pass in a diagramId and get a list of all the parts associated with the diagram. Again, thanks so much for all the replies and suggestions. -
Well done!
But beware that ISNUMERIC is not always trustworthy.
select
isnumeric('1E1'), ISNUMERIC('1D1')
both returns 1.
That's why I wrote my suggestion in another fashion.
N 56°04'39.16"
E 12°55'05.25" -
It even thinks a tab (Char(9)) is a valid numeric.
-
> it could be a mix (11, 11A, 11B, 12) and it can even be alpha leading (S1, S2, etc.).
Just wait for 123 which is 12+3, and must go after 12 and before 12A.
_____________
Code for TallyGenerator
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply