PostgreSQL: Sorting letters before numbers
I would like to order a bpchar column in the following order (first order by a-z, then numbers):
abc
bcd
xrf/1
zyd
0/abc
0/bdc
0/efg
How could I accomplish that?
Thank you
postgresql sql-order-by
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
add a comment |
I would like to order a bpchar column in the following order (first order by a-z, then numbers):
abc
bcd
xrf/1
zyd
0/abc
0/bdc
0/efg
How could I accomplish that?
Thank you
postgresql sql-order-by
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
add a comment |
I would like to order a bpchar column in the following order (first order by a-z, then numbers):
abc
bcd
xrf/1
zyd
0/abc
0/bdc
0/efg
How could I accomplish that?
Thank you
postgresql sql-order-by
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
I would like to order a bpchar column in the following order (first order by a-z, then numbers):
abc
bcd
xrf/1
zyd
0/abc
0/bdc
0/efg
How could I accomplish that?
Thank you
postgresql sql-order-by
postgresql sql-order-by
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
asked Nov 16 '18 at 7:49
user3193317user3193317
46211
46211
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Can't fully tell from your question what you actually want. If it is the first character of the string you want to check whether it's numeric or alphabet, you may use a CASE expression in ORDER BY like this.
select * FROM t ORDER BY
CASE
WHEN col ~ '^[a-zA-Z]' THEN 1
WHEN col ~ '^[0-9]' THEN 2
END,col;
Demo
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
Thanks, yes thats it!
– user3193317
Nov 16 '18 at 12:29
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333519%2fpostgresql-sorting-letters-before-numbers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Can't fully tell from your question what you actually want. If it is the first character of the string you want to check whether it's numeric or alphabet, you may use a CASE expression in ORDER BY like this.
select * FROM t ORDER BY
CASE
WHEN col ~ '^[a-zA-Z]' THEN 1
WHEN col ~ '^[0-9]' THEN 2
END,col;
Demo
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
Thanks, yes thats it!
– user3193317
Nov 16 '18 at 12:29
add a comment |
Can't fully tell from your question what you actually want. If it is the first character of the string you want to check whether it's numeric or alphabet, you may use a CASE expression in ORDER BY like this.
select * FROM t ORDER BY
CASE
WHEN col ~ '^[a-zA-Z]' THEN 1
WHEN col ~ '^[0-9]' THEN 2
END,col;
Demo
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
Thanks, yes thats it!
– user3193317
Nov 16 '18 at 12:29
add a comment |
Can't fully tell from your question what you actually want. If it is the first character of the string you want to check whether it's numeric or alphabet, you may use a CASE expression in ORDER BY like this.
select * FROM t ORDER BY
CASE
WHEN col ~ '^[a-zA-Z]' THEN 1
WHEN col ~ '^[0-9]' THEN 2
END,col;
Demo
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
Can't fully tell from your question what you actually want. If it is the first character of the string you want to check whether it's numeric or alphabet, you may use a CASE expression in ORDER BY like this.
select * FROM t ORDER BY
CASE
WHEN col ~ '^[a-zA-Z]' THEN 1
WHEN col ~ '^[0-9]' THEN 2
END,col;
Demo
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
answered Nov 16 '18 at 11:47
Kaushik NayakKaushik Nayak
20.9k41332
20.9k41332
Thanks, yes thats it!
– user3193317
Nov 16 '18 at 12:29
add a comment |
Thanks, yes thats it!
– user3193317
Nov 16 '18 at 12:29
Thanks, yes thats it!
– user3193317
Nov 16 '18 at 12:29
Thanks, yes thats it!
– user3193317
Nov 16 '18 at 12:29
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333519%2fpostgresql-sorting-letters-before-numbers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
