formating a phone number in the postresql query
1
Can you format a phone number in an postgreSQL query? I have a phone number column. The phone numbers are held as such: 1234567890. I am wondering if postgres will format to (123) 456-7890. I can do this outside the query, I am using php, but it would be nice if I was able to have the output of the query like (123) 456-7890
sql postgresql
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
add a comment |
1
Can you format a phone number in an postgreSQL query? I have a phone number column. The phone numbers are held as such: 1234567890. I am wondering if postgres will format to (123) 456-7890. I can do this outside the query, I am using php, but it would be nice if I was able to have the output of the query like (123) 456-7890
sql postgresql
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
So, it is stored as a number, not as a String, correct?
– Radu Gheorghiu
Oct 28 '13 at 19:17
yes, it is number
– Jeff Kyzer
Oct 28 '13 at 19:20
3
Um, phone numbers are not actually numbers, they're really strings that just happen to consist of digit characters. Fix your schema first. Do you have to deal with international numbers or just North American ones? What about extensions?
– mu is too short
Oct 28 '13 at 19:25
Just a quick comment in that I recommend you enforce the right format through the web-interface with let say JQuery or Javascrip--then store that string directly into the DB. I know this doesn't answer your question, but it's definitely better design, I would say :)
– Roberto Navarro
Oct 28 '13 at 19:38
add a comment |
1
1
1
Can you format a phone number in an postgreSQL query? I have a phone number column. The phone numbers are held as such: 1234567890. I am wondering if postgres will format to (123) 456-7890. I can do this outside the query, I am using php, but it would be nice if I was able to have the output of the query like (123) 456-7890
sql postgresql
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
Can you format a phone number in an postgreSQL query? I have a phone number column. The phone numbers are held as such: 1234567890. I am wondering if postgres will format to (123) 456-7890. I can do this outside the query, I am using php, but it would be nice if I was able to have the output of the query like (123) 456-7890
sql postgresql
sql postgresql
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
asked Oct 28 '13 at 19:15
Jeff KyzerJeff Kyzer
3061413
3061413
So, it is stored as a number, not as a String, correct?
– Radu Gheorghiu
Oct 28 '13 at 19:17
yes, it is number
– Jeff Kyzer
Oct 28 '13 at 19:20
3
Um, phone numbers are not actually numbers, they're really strings that just happen to consist of digit characters. Fix your schema first. Do you have to deal with international numbers or just North American ones? What about extensions?
– mu is too short
Oct 28 '13 at 19:25
Just a quick comment in that I recommend you enforce the right format through the web-interface with let say JQuery or Javascrip--then store that string directly into the DB. I know this doesn't answer your question, but it's definitely better design, I would say :)
– Roberto Navarro
Oct 28 '13 at 19:38
add a comment |
So, it is stored as a number, not as a String, correct?
– Radu Gheorghiu
Oct 28 '13 at 19:17
yes, it is number
– Jeff Kyzer
Oct 28 '13 at 19:20
3
Um, phone numbers are not actually numbers, they're really strings that just happen to consist of digit characters. Fix your schema first. Do you have to deal with international numbers or just North American ones? What about extensions?
– mu is too short
Oct 28 '13 at 19:25
Just a quick comment in that I recommend you enforce the right format through the web-interface with let say JQuery or Javascrip--then store that string directly into the DB. I know this doesn't answer your question, but it's definitely better design, I would say :)
– Roberto Navarro
Oct 28 '13 at 19:38
So, it is stored as a number, not as a String, correct?
– Radu Gheorghiu
Oct 28 '13 at 19:17
So, it is stored as a number, not as a String, correct?
– Radu Gheorghiu
Oct 28 '13 at 19:17
yes, it is number
– Jeff Kyzer
Oct 28 '13 at 19:20
yes, it is number
– Jeff Kyzer
Oct 28 '13 at 19:20
3
3
Um, phone numbers are not actually numbers, they're really strings that just happen to consist of digit characters. Fix your schema first. Do you have to deal with international numbers or just North American ones? What about extensions?
– mu is too short
Oct 28 '13 at 19:25
Um, phone numbers are not actually numbers, they're really strings that just happen to consist of digit characters. Fix your schema first. Do you have to deal with international numbers or just North American ones? What about extensions?
– mu is too short
Oct 28 '13 at 19:25
Just a quick comment in that I recommend you enforce the right format through the web-interface with let say JQuery or Javascrip--then store that string directly into the DB. I know this doesn't answer your question, but it's definitely better design, I would say :)
– Roberto Navarro
Oct 28 '13 at 19:38
Just a quick comment in that I recommend you enforce the right format through the web-interface with let say JQuery or Javascrip--then store that string directly into the DB. I know this doesn't answer your question, but it's definitely better design, I would say :)
– Roberto Navarro
Oct 28 '13 at 19:38
add a comment |
3 Answers
3
active
oldest
votes
1
Use SUBSTRING function
something like:
SELECT
'(' || SUBSTRING((PhoneNumber, 1, 3) + ') ' || SUBSTRING(PhoneNumber, 4,3) || '-' || SUBSTRING((PhoneNumber,7,4)
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
I don't think you can append strings in postgres by using+
– Radu Gheorghiu
Oct 28 '13 at 19:31
sorry, I have fixed || instead of +
– Emmanuel N
Oct 28 '13 at 19:36
add a comment |
3
This will work for you:
SELECT
'( ' || SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 1 FOR 3) || ' ) '
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 4 FOR 3) || '-'
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 7 FOR LENGTH(CAST(NUMBER AS VARCHAR)))
FROM
YOURTABLE
Also, here is a SQLFiddle.
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
Thank you very much
– Jeff Kyzer
Oct 28 '13 at 19:32
@Kyzer If you have found an answer, never forget to upvote/mark it as the correct answer, so others can find it easily.
– Radu Gheorghiu
Oct 28 '13 at 19:34
add a comment |
0
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' ||
SUBSTRING(phone FROM 4 FOR 3) || '-' ||
SUBSTRING(phone FROM 7 FOR 4)
FROM table;
This will return all phone numbers with the format (123) 456-7890, however there will be no column header. To remedy this, use the following SELECT statement:
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' || SUBSTRING(phone FROM 4 FOR 3) || '-' || SUBSTRING(phone FROM 7 FOR 4) AS Phone FROM chapters;
Hope this helps!
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
1
Please format code as described in stackoverflow.com/help/formatting, it makes it easier to read for everyone
– Nick
Nov 13 '18 at 10:59
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
});
}
});
draft saved
draft discarded
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%2f19643232%2fformating-a-phone-number-in-the-postresql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Use SUBSTRING function
something like:
SELECT
'(' || SUBSTRING((PhoneNumber, 1, 3) + ') ' || SUBSTRING(PhoneNumber, 4,3) || '-' || SUBSTRING((PhoneNumber,7,4)
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
I don't think you can append strings in postgres by using+
– Radu Gheorghiu
Oct 28 '13 at 19:31
sorry, I have fixed || instead of +
– Emmanuel N
Oct 28 '13 at 19:36
add a comment |
1
Use SUBSTRING function
something like:
SELECT
'(' || SUBSTRING((PhoneNumber, 1, 3) + ') ' || SUBSTRING(PhoneNumber, 4,3) || '-' || SUBSTRING((PhoneNumber,7,4)
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
I don't think you can append strings in postgres by using+
– Radu Gheorghiu
Oct 28 '13 at 19:31
sorry, I have fixed || instead of +
– Emmanuel N
Oct 28 '13 at 19:36
add a comment |
1
1
1
Use SUBSTRING function
something like:
SELECT
'(' || SUBSTRING((PhoneNumber, 1, 3) + ') ' || SUBSTRING(PhoneNumber, 4,3) || '-' || SUBSTRING((PhoneNumber,7,4)
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
Use SUBSTRING function
something like:
SELECT
'(' || SUBSTRING((PhoneNumber, 1, 3) + ') ' || SUBSTRING(PhoneNumber, 4,3) || '-' || SUBSTRING((PhoneNumber,7,4)
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
edited Oct 28 '13 at 19:35
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
answered Oct 28 '13 at 19:24
Emmanuel NEmmanuel N
5,97921935
5,97921935
I don't think you can append strings in postgres by using+
– Radu Gheorghiu
Oct 28 '13 at 19:31
sorry, I have fixed || instead of +
– Emmanuel N
Oct 28 '13 at 19:36
add a comment |
I don't think you can append strings in postgres by using+
– Radu Gheorghiu
Oct 28 '13 at 19:31
sorry, I have fixed || instead of +
– Emmanuel N
Oct 28 '13 at 19:36
I don't think you can append strings in postgres by using
+– Radu Gheorghiu
Oct 28 '13 at 19:31
I don't think you can append strings in postgres by using
+– Radu Gheorghiu
Oct 28 '13 at 19:31
sorry, I have fixed || instead of +
– Emmanuel N
Oct 28 '13 at 19:36
sorry, I have fixed || instead of +
– Emmanuel N
Oct 28 '13 at 19:36
add a comment |
3
This will work for you:
SELECT
'( ' || SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 1 FOR 3) || ' ) '
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 4 FOR 3) || '-'
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 7 FOR LENGTH(CAST(NUMBER AS VARCHAR)))
FROM
YOURTABLE
Also, here is a SQLFiddle.
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
Thank you very much
– Jeff Kyzer
Oct 28 '13 at 19:32
@Kyzer If you have found an answer, never forget to upvote/mark it as the correct answer, so others can find it easily.
– Radu Gheorghiu
Oct 28 '13 at 19:34
add a comment |
3
This will work for you:
SELECT
'( ' || SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 1 FOR 3) || ' ) '
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 4 FOR 3) || '-'
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 7 FOR LENGTH(CAST(NUMBER AS VARCHAR)))
FROM
YOURTABLE
Also, here is a SQLFiddle.
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
Thank you very much
– Jeff Kyzer
Oct 28 '13 at 19:32
@Kyzer If you have found an answer, never forget to upvote/mark it as the correct answer, so others can find it easily.
– Radu Gheorghiu
Oct 28 '13 at 19:34
add a comment |
3
3
3
This will work for you:
SELECT
'( ' || SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 1 FOR 3) || ' ) '
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 4 FOR 3) || '-'
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 7 FOR LENGTH(CAST(NUMBER AS VARCHAR)))
FROM
YOURTABLE
Also, here is a SQLFiddle.
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
This will work for you:
SELECT
'( ' || SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 1 FOR 3) || ' ) '
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 4 FOR 3) || '-'
|| SUBSTRING(CAST(NUMBER AS VARCHAR) FROM 7 FOR LENGTH(CAST(NUMBER AS VARCHAR)))
FROM
YOURTABLE
Also, here is a SQLFiddle.
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
answered Oct 28 '13 at 19:31
Radu GheorghiuRadu Gheorghiu
13.4k124979
13.4k124979
Thank you very much
– Jeff Kyzer
Oct 28 '13 at 19:32
@Kyzer If you have found an answer, never forget to upvote/mark it as the correct answer, so others can find it easily.
– Radu Gheorghiu
Oct 28 '13 at 19:34
add a comment |
Thank you very much
– Jeff Kyzer
Oct 28 '13 at 19:32
@Kyzer If you have found an answer, never forget to upvote/mark it as the correct answer, so others can find it easily.
– Radu Gheorghiu
Oct 28 '13 at 19:34
Thank you very much
– Jeff Kyzer
Oct 28 '13 at 19:32
Thank you very much
– Jeff Kyzer
Oct 28 '13 at 19:32
@Kyzer If you have found an answer, never forget to upvote/mark it as the correct answer, so others can find it easily.
– Radu Gheorghiu
Oct 28 '13 at 19:34
@Kyzer If you have found an answer, never forget to upvote/mark it as the correct answer, so others can find it easily.
– Radu Gheorghiu
Oct 28 '13 at 19:34
add a comment |
0
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' ||
SUBSTRING(phone FROM 4 FOR 3) || '-' ||
SUBSTRING(phone FROM 7 FOR 4)
FROM table;
This will return all phone numbers with the format (123) 456-7890, however there will be no column header. To remedy this, use the following SELECT statement:
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' || SUBSTRING(phone FROM 4 FOR 3) || '-' || SUBSTRING(phone FROM 7 FOR 4) AS Phone FROM chapters;
Hope this helps!
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
1
Please format code as described in stackoverflow.com/help/formatting, it makes it easier to read for everyone
– Nick
Nov 13 '18 at 10:59
add a comment |
0
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' ||
SUBSTRING(phone FROM 4 FOR 3) || '-' ||
SUBSTRING(phone FROM 7 FOR 4)
FROM table;
This will return all phone numbers with the format (123) 456-7890, however there will be no column header. To remedy this, use the following SELECT statement:
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' || SUBSTRING(phone FROM 4 FOR 3) || '-' || SUBSTRING(phone FROM 7 FOR 4) AS Phone FROM chapters;
Hope this helps!
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
1
Please format code as described in stackoverflow.com/help/formatting, it makes it easier to read for everyone
– Nick
Nov 13 '18 at 10:59
add a comment |
0
0
0
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' ||
SUBSTRING(phone FROM 4 FOR 3) || '-' ||
SUBSTRING(phone FROM 7 FOR 4)
FROM table;
This will return all phone numbers with the format (123) 456-7890, however there will be no column header. To remedy this, use the following SELECT statement:
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' || SUBSTRING(phone FROM 4 FOR 3) || '-' || SUBSTRING(phone FROM 7 FOR 4) AS Phone FROM chapters;
Hope this helps!
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' ||
SUBSTRING(phone FROM 4 FOR 3) || '-' ||
SUBSTRING(phone FROM 7 FOR 4)
FROM table;
This will return all phone numbers with the format (123) 456-7890, however there will be no column header. To remedy this, use the following SELECT statement:
SELECT '(' || SUBSTRING(phone FROM 1 FOR 3) || ') ' || SUBSTRING(phone FROM 4 FOR 3) || '-' || SUBSTRING(phone FROM 7 FOR 4) AS Phone FROM chapters;
Hope this helps!
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
edited Nov 13 '18 at 11:03
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
answered Nov 13 '18 at 10:52
hi-tek-rednekhi-tek-rednek
11
11
1
Please format code as described in stackoverflow.com/help/formatting, it makes it easier to read for everyone
– Nick
Nov 13 '18 at 10:59
add a comment |
1
Please format code as described in stackoverflow.com/help/formatting, it makes it easier to read for everyone
– Nick
Nov 13 '18 at 10:59
1
1
Please format code as described in stackoverflow.com/help/formatting, it makes it easier to read for everyone
– Nick
Nov 13 '18 at 10:59
Please format code as described in stackoverflow.com/help/formatting, it makes it easier to read for everyone
– Nick
Nov 13 '18 at 10:59
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f19643232%2fformating-a-phone-number-in-the-postresql-query%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
So, it is stored as a number, not as a String, correct?
– Radu Gheorghiu
Oct 28 '13 at 19:17
yes, it is number
– Jeff Kyzer
Oct 28 '13 at 19:20
3
Um, phone numbers are not actually numbers, they're really strings that just happen to consist of digit characters. Fix your schema first. Do you have to deal with international numbers or just North American ones? What about extensions?
– mu is too short
Oct 28 '13 at 19:25
Just a quick comment in that I recommend you enforce the right format through the web-interface with let say JQuery or Javascrip--then store that string directly into the DB. I know this doesn't answer your question, but it's definitely better design, I would say :)
– Roberto Navarro
Oct 28 '13 at 19:38