how to take the numerical asc order wise in nvarchar column

Arutprakasam 261 Reputation points
2023-11-24T13:31:01.6933333+00:00

Hi,

I need the get the following column values in asc order based on numeric content,

Column value

op_100

op_10A

op_10

op_20

op_20B

op_300

op_310

I want to get in the following order,

op_10

op_10A

op_20

op_20B

op_100

op_300

op_310

Kindly please share the SQL select query

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,339 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
{count} votes

Accepted answer
  1. Viorel 114.7K Reputation points
    2023-11-24T15:13:11.42+00:00

    If the prefix is “op_” and the suffix is “A”, “B”, “C”, “D”, “E” or “F”, then:

    select * 
    from MyTable 
    order by cast(translate(substring(mycolumn1, 4, 100), 'ABCDEF', '      ') as int), len(trim(mycolumn1))
    

    In case of any prefix and suffix:

    select * 
    from MyTable
    order by cast(translate(mycolumn1, translate(trim(mycolumn1), '0123456789', 'xxxxxxxxxx'), replicate(' ', len(trim(mycolumn1)))) as int), len(trim(mycolumn1))
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful