I just hope t-sql someday can be as powerful as Oracle PL/SQL which contains RegEx support. The only thing we can do is to solve the issue within our power. Sometimes, as the receiver at the end of the consuming chain, it is impossible to ask such alphanumeric string owners to change anything. In real world, this kind of alphanumeric strings are very common.
We have people who store Vendor 'Numbers' as AlphaNumeric - we handle it this way.ĬASE WHEN isnumeric(vendor) = 1 THEN str(convert(float,Vendor))
SORT NUMERIC FIELDS AQUAMACS HOW TO
Good solution, however, on how to generate the correct sorting string. That way, the "hit" of processing a million rows is absorbed by the process of loading these rows in the first place, and is not incurred when the report needs to be run multiple times. If at all possible, I would recommend incorporating your final solution into the first step ("Once the data is loaded into a table"). Wednesday, Septem1:03:23 PM - Daniel Bragg Please read the following related articles and share your own ideas about The performance of our current T-SQL version function. To utilize the powerful RegEx capability in T-SQL and this will surely improve T-SQL currently does not support Regular Expression, so we may implement a CLR function Regular Expression to find and extract numbers in a string. To improve the function mentioned in the tip, it is better to use Name) for deployment is the first step for a successful deployment.
SORT NUMERIC FIELDS AQUAMACS FULL
So ordering these scripts by full names (i.e. Names or folder names contain alphanumeric strings to indicate the execution Such as when we have multiple scripts stored in multiple folders, and the script In this tip, we discussed how to order numeric values within alphanumeric strings using T-SQL, this technique is obviously not very efficient because theįunction needs to process the string value of each row in an internal loop, so whenĪ table has millions of rows, this method will be very time-consuming.īut this alphanumeric string ordering is critical in some DBA daily operations, We will use the data below and do the sorting test. Test the User Defined Function to Order Numbers within a String So the whole number's length is and set to empty string and exit Then extract this number, pad the letter to the left of this extracted number from to the end of the string, it is a whole number, 0 – 9) is found, mark its position as Starting from find the next non-numeric letter, if found, mark its positionĪs and then retrieve the number between and pad the letter the left of this extracted number so the whole number's length is string to start from the end of the number and then repeat step 3
Letter to the left of N string to make the final N string to be of length Here is the UDF, it will search through the source string and pad specified This can be omitted as we can use '0'Īs the default, but I left it there so we can have more flexibility, for example, weĬan use '9' as default, and this will cause the sorting in descending So the key here is to come up with a solution to standardize all source strings. This is like standardizing all strings for comparison, and once all stringsĪre "standardized", we can sort the strings to get the The longest N string of this source string is "56789", if we pad 0 to each of the N string to make each N string to have a length of 6, we will N string to make the N string length to be bigger than the largest N string lengthįor example, if we have an alphanumeric string like the following: So the algorithm to make "10.abc" and "2.abc" strings to be in the orderĮxpected via T-SQL can be designed as follows.įor each N string inside the source string, we will pad 0 to the left of the SQL Server T-SQL User Defined Function to Order Numbers within a String