CODEWITHSUNDEEP
phpsqlsorting
rosc0
rosc0

Reputation: 1

Sorting in a sql query, disregarding the numbers at the start of the field?

I need to sort items on a website in a bit of a weird way:

Just say my data is: 

681A
500AB
300BB

i need it to be in this order, so i need it to disregard the number at the start and order by the first letter it encounters.

Is there a way to do this in a sql query? Or will i have to create an array on the page and sort it that way with php?

Thanks in advance if anyone can help out.

Upvotes: 0

Views: 104

Answers (5)

user527908
user527908

Reputation: 11

select * from table order by substring(NNNN,startposition,endposition) asc

Upvotes: 0

Flavio CF Oliveira
Flavio CF Oliveira

Reputation: 5285

Take a look how to make a regex replacement on sql or try to use replace function some thing like this:

SELECT * FROM (
    SELECT REPLACE([YourCol],'0','') AS COL, *
    FROM [table]
) as T
ORDER BY [COL]
  • note that regex does not has a great performance

Upvotes: 0

trickwallett
trickwallett

Reputation: 2468

If the prefix number is always 3 digits long, then this would be reasonable:

SELECT SUBSTR(field1,4) AS shortfield1
FROM table
ORDER BY shortcol

see

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157839

You have to create additional column in your table, storing these abbreviations in the form you'd like to be sorted.

Upvotes: 0

j_freyre
j_freyre

Reputation: 4738

A way you can try to do that is to add a substring in your query and use it to sort.

Note that this will work only if you always have 3 char to ignore.

SELECT fieldA, fieldB, SUBSTRING(fieldB,4) as subfieldB FROM myTable order by subsfieldB

Upvotes: 1

Related Questions