devxlogo

Sorting “Codes” within a Column

Sorting “Codes” within a Column

Question:
I have a table that consists of numbers stored as characters.How can I sort them in numerical order? I can’t convert thisfield to numeric because some content is alpha (12b).

For example, I want ‘6500 900 34 1200′ to be sortedas ’34 900 1200 6500.’

Answer:
If the fields are of consistent format, you could use a substring function to pull out the appropriate portions in the correct order. (The syntax for the substring function varies between products, so you’ll need to check your documentation for the exact usage.) Here’s an example:

    SELECT SUBSTRING (column FROM 10 FOR 2) AS col1,            SUBSTRING (column FROM 6 FOR 3) AS col2,            SUBSTRING (column FROM 13 FOR 4) AS col3,            SUBSTRING (column FROM 1 FOR 4) AS col4        FROM Table        ORDER BY col1, col2, col3, col4;

devx-admin

Share the Post: