In this example
patindex gets the position of first numeric character using the [0-9] wild card expression, then substring gets the part of string from first numeric character to end of the string which is appended with 4 '0's from left.
then RIGHT gets the last 5 character of the newly formed substring.
if the string is abc1
then the string will be now '00001'
or
if the string is abc12
then the string will be now '00012'
etc.
Hence, we can use this expression for Natural sorting.
example:
ORDER BY RIGHT( '0000' + SUBSTRING( L_ID , ISNULL( NULLIF( PATINDEX( '%[0-9]%' , L_ID
) , 0
) , LEN( L_ID
) + 1
) , LEN( L_ID
)
) , 5
)
Patindex:
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
PATINDEX ( '%pattern%' , expression )
Right:
Returns the right part of a character string with the specified number of characters.
RIGHT ( character_expression , integer_expression )
[]Wild Card for Patindex:
Matches any single character within the specified range or set that is specified between the brackets. These wildcard characters can be used in string comparisons that involve pattern matching, such as LIKE and PATINDEX.