Phone Number display method, sql query

Question

Please help me with the below query,please modfiy and let me know,I use ssms

I need to display only Phonenumber. Below is the query I'm using as of now. I divide it using two columns because there might be cell number and home phone.

 CONCAT(RIGHT(LEFT(HR_PN_1.PhoneNumberID, 4), 3), REPLACE(RIGHT(HR_PN_2.PhoneNumberID, 8), '-', '')) as 'PersonPhone',
    CASE
        WHEN HR_PN_2.PhoneNumberID is not null
    THEN CONCAT(RIGHT(LEFT(HR_PN_2.PhoneNumberID, 4), 3), REPLACE(RIGHT(HR_PN_2.PhoneNumberID, 8), '-', ''))
    END as 'PersonWorkPhone',

Example of data in the PhoneNumnberID column, I have in my table HR_PN_1 and HR_PN_2 is as follows:

(123)456-7890TOMMY
(123)456-7890 DAD
(123)456-7890 ***
(123)456-7890 2300
(123)456-7890CELL#
(123)456-7890

I need these numbers to be displayed as only in phone number format (123)456-7890

Why do you have other data stored in the same column to begin with? A phone number column should contain nothing but the phone number. If you have a PIN or other information, put it in its own column. If you do so, you won't face these sorts of issues. — Ken White, Oct 16 '18 at 18:28
Are you using MySQL or MS SQL Server? Both are different RDBMS with lots of syntax variations — Madhur Bhaiya, Oct 16 '18 at 18:33
@Ken White I can't do it on my side, It's been done by registration people. They always make mistake.I hope you understood. — kreme123, Oct 16 '18 at 18:34
@paparazzo,I didn't get your question exactly..Could you brief me ? — kreme123, Oct 16 '18 at 18:37
You're tag spamming. You've got both MySQL and SQL Server. They're not the same. One is from Oracle, the other from Microsoft. Remove the tag that does not apply to your question. — Ken White, Oct 16 '18 at 18:40
*They always make mistake* They couldn't, if your database was designed properly, because the phone number column would be the proper size and wouldn't have room for extra data. Your application could also limit the input and do validation of the data. — Ken White, Oct 16 '18 at 18:42
@KenWhite It's a meditech software EMR application , So we can't perform any chnages as of now. — kreme123, Oct 16 '18 at 18:45
The question is unclear. What are your expected results given the sample data. why not just `SELECT left(PhoneNumberID,13)`? of if you just need the numbers `replace(replacereplace(left(PhoneNumberID,13),'(',''),')',''),'-','')` or use a [UDF](https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) to only pull back #'s from the 1st 13? or `select substring([col], PatIndex('%[0-9]%', [col], len([col])) as number from [tab]`; — xQbert, Oct 16 '18 at 18:49
@xQbert Could you please update me new query completely by editing mine from above please. — kreme123, Oct 16 '18 at 19:10
@kreme123 as you've not posted a whole query: no. Now if you want to pivot the data use a case statement and evaluate the characters to the right of the 13'th position to determine the Type of phone number. how do you tell "Cell" from "Home" phone? — xQbert, Oct 16 '18 at 19:14
@xQbert we have additional column in that table called type of phone like home or cell or work — kreme123, Oct 16 '18 at 19:20
@xQbert .. can you exactly brief me once again replace line query.. its giving me error here — kreme123, Oct 16 '18 at 19:20

Gordon Linoff · Answer 1 · 2018-10-16T18:55:01.257

3

If those are the values in the column, then you would seem to want:

LEFT(HR_PN_1.PhoneNumberID, 13)

edited Oct 16 '18 at 18:55

answered Oct 16 '18 at 18:52

Gordon Linoff

1,242,037
58
646
786

I think there's 13...or did SQL Server go to zero based? but this is where I went; but thought I was missing something. – xQbert Oct 16 '18 at 18:53

score 0 · Answer 2 · answered Oct 16 '18 at 19:19

If those phonenumbers are always at the start then a simple LEFT will suffice.

And if the position of the phonenumber can change, but it's always in the same format?
Then you could combine SUBSTRING with PATINDEX.

SUBSTRING(PhoneNumberID, PATINDEX('%([0-9][0-9][0-9])[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%', PhoneNumberID), 13)

Then wrapping it in a CASE just to avoid returning something that's not a phonenumber.

Example snippet :

declare @HR_PN_1 table (HR_ID int identity (1,1) primary key, PhoneNumberID varchar(100));
declare @HR_PN_2 table (HR_ID int identity (1,1) primary key, PhoneNumberID varchar(100));

insert into @HR_PN_1 (PhoneNumberID) values
('(123)456-7891TOMMY'),
('(123)456-7892 DAD'),
('(123)456-7893 ***'),
('(123)456-7894 2300'),
('(123)456-7895CELL#'),
('(123)456-7896'),
('no phone');

insert into @HR_PN_2 (PhoneNumberID) 
select PhoneNumberID 
from @HR_PN_1
order by HR_ID desc;

SELECT hr1.HR_ID,
(CASE 
 WHEN hr1.PhoneNumberID like '%([0-9][0-9][0-9])[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%' 
 THEN SUBSTRING(hr1.PhoneNumberID, PATINDEX('%([0-9][0-9][0-9])[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%', hr1.PhoneNumberID), 13)
 END) AS "PersonPhone",
(CASE
 WHEN hr2.PhoneNumberID like '%([0-9][0-9][0-9])[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%' 
 THEN SUBSTRING(hr2.PhoneNumberID, PATINDEX('%([0-9][0-9][0-9])[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9]%', hr2.PhoneNumberID), 13)
 END) AS "PersonWorkPhone"
FROM @HR_PN_1 AS hr1
LEFT JOIN @HR_PN_2 AS hr2 ON (hr2.HR_ID = hr1.HR_ID);

score 0 · Accepted Answer · answered Oct 16 '18 at 19:20

0

How about this?

SELECT SUBSTRING(yourcolumn, PATINDEX('%([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%',yourcolumn), 13)
FROM yourtable

answered Oct 16 '18 at 19:20

jigga

558
4
16

Phone Number display method, sql query

3 Answers3