SQL order by integer within a string

Question

I have a string that my result is sorting by. It is composed of a room number, resident name, and a few other fields.

Example:

460, Bob Jones, ......
5, John Doe, .....

The problem is, when I order this way, it puts large numbers that begin with a small number ahead of the small numbers that begin with a large one (ie, 460 is placed before 5). How can I have sql process the entire integer in the string and sort based on the entire number, not just the first digit value?

A string is not an integer and it'll sort numbers as 1,10,11,2,20,3,4,44,etc if they are stored as a string (varchar)... First comment is to reconsider your database use, storing strings with integer values mixed with data is horrid practice. If database structure is outside of your scope...using substrings you can pick out the integer before the first comma, convert to an integer, and sort on that. What database are you on? String functions vary between them — Twelfth
– Twelfth, Commented Sep 2, 2014 at 23:37

Gordon Linoff · Accepted Answer · 2014-09-02 23:39:44Z

1

You can do this by converting the initial part of the string to a number. However, this depends on the database. For instance, in MySQL, you can convert the value to an integer just by adding + 0:

order by string + 0

In other databases, you would need to part up to the comma and do a conversion. For instance, in SQL Server:

order by cast(left(string, charindex(',', string) - 1) as int)

answered Sep 2, 2014 at 23:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL order by integer within a string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related