# Since no one answers on TOF

**URL:** <https://ifnotnil.com/t/since-no-one-answers-on-tof/4191>\
**Category:** Other\
**Created:** [3 December 2024 03:57 UTC](https://ifnotnil.com/t/since-no-one-answers-on-tof/4191 "2024-12-03T03:57:24Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![npalardy](https://ifnotnil.com/user_avatar/ifnotnil.com/npalardy/32/10_2.png) [@npalardy](https://ifnotnil.com/u/npalardy)\
**Post date:** [3 December 2024 03:57 UTC](https://ifnotnil.com/t/since-no-one-answers-on-tof/4191/1 "2024-12-03T03:57:24Z")

</div>

> **[PostgreSQL How to sort apha numeric columns?](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441)**
>
> Hello all, I have a column that is of type text. It contains data such as: A1 A10 A100 A300 B1 B10 C001 C010 Any ideas how to construct a SQL query so that these are ordered Correctly, first by Alpha, then by the number following? Tim

```auto
-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'C1', 'Sales');
INSERT INTO EMPLOYEE VALUES (0005, 'A10', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'C10','Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'C100', 'Sales');
INSERT INTO EMPLOYEE VALUES (0004, 'A100', 'Sales');

-- fetch 
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';

select * from employee
order by btrim ( name , '01234567890'), btrim ( upper(name) , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') ;

```

Is it perfect ? no  
Theres lots I dunno about his set up

but its AN answer that maybe he’ll see

---

<div class="post-metadata">

**Author:** ![DaveDuke](https://ifnotnil.com/letter_avatar_proxy/v4/letter/d/8e8cbc/32.png) [@DaveDuke](https://ifnotnil.com/u/DaveDuke)\
**Post date:** [3 December 2024 13:28 UTC](https://ifnotnil.com/t/since-no-one-answers-on-tof/4191/2 "2024-12-03T13:28:30Z")

</div>

SELECT \*  
FROM EMPLOYEE  
ORDER BY  
btrim(name, ‘0123456789’) ASC, – Extract the alphabetical prefix  
CAST(btrim(name, ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’) AS INTEGER) ASC; – Extract and convert the numeric suffix