Phone Number Display Method, SQL Query

Phone Number display method, sql query

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

SQL Query To Display Mobile number that are registered in Different Mobile Network

I think you need to use GROUP BY/HAVING:

SELECT  MobileNo
FROM MobileUsers
GROUP BY MobileNo
HAVING COUNT(DISTINCT networkname) > 1 -- MORE THAN ONE NETWORK

If you then need to get all records back for these mobile numbers, you would need to put the above in a subquery and join back to it:

SELECT  m.*
FROM MobileUsers m
INNER JOIN
( SELECT MobileNo
FROM MobileUsers
GROUP BY MobileNo
HAVING COUNT(DISTINCT networkname) > 1 -- MORE THAN ONE NETWORK
) Dupe
ON dupe.MobileNo = m.MobileNo

SQL - Query Phonenumber that are stored inconsistently

Since I don't know what RDBMS you're looking for, I'll give the most generic way:

phonenumber like '%5%5%5%1%2%3%4%5%6%'

This assumes that all phone numbers are at least equal length (in digits).

How do I write an efficient query to retrieve phone numbers attached to an ID?

I finally used @Matteo Zanoni's method:

SELECT "ID", "Phone_Number"
FROM Table
WHERE "id" IN (
'7559',
'1754',
...
)

Formatting Phone Number to US Format (###) ###-####

Since one suggestion was made already and the suggestion there to isolate numbers in a string uses a while loop I need to post an alternative to that which doesn't use any looping. Instead it utilizes a tally or numbers table. There are lots of solutions for those. I like to use a view which is lightning fast and has zero reads.

Here is my version of a tally table.

create View [dbo].[cteTally] as

WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally

Next we need a table valued function to remove the characters that are not numbers using our tally table. This is also super fast because we are using our tally table instead of looping.

create function GetOnlyNumbers
(
@SearchVal varchar(8000)
) returns table as return

with MyValues as
(
select substring(@SearchVal, N, 1) as number
, t.N
from cteTally t
where N <= len(@SearchVal)
and substring(@SearchVal, N, 1) like '[0-9]'
)

select distinct NumValue = STUFF((select number + ''
from MyValues mv2
order by mv2.N
for xml path('')), 1, 0, '')
from MyValues mv

Now that we have all the legwork done we can focus on the task at hand. Since you didn't provide any sample data I just made up some stuff. I am not really sure if this is representative of your data or not but this works on the sample data I created.

if OBJECT_ID('tempdb..#Something') is not null
drop table #Something

create table #Something(SomeVal varchar(100))

insert #Something values
('Maybe you have other stuff in here. 5552223333 additional characters can cause grief')
, ('321-654-9878')
, ('123)-333-4444')
, ('1234567')

select replace(format(try_convert(bigint, n.NumValue), '(###) ###-####'), '() ', '')
, n.NumValue
from #Something s
cross apply dbo.GetOnlyNumbers(s.SomeVal) n

The output for the formatted data looks like this:

(555) 222-3333
(321) 654-9878
(123) 333-4444
123-4567

How to store a phone number in SQL as an integer?

Don't do that, phone numbers are not integers for very good reasons :

  • leading zeroes : 0199857514
  • country codes : +34199857514
  • special commands : #992,514
  • sip phones : sip:01199857514@stackoverflow.net

If you're going to store your phone numbers as integers, you are going to have major limitations and problems later.

To answer your question: You can't store a phone number as an integer, you must use a string.

Changing all phone numbers SQL

In the version of SQL Server 2008 I have (r2) it seems the parameters of the substring function are actually (stringname, start location, length). Therefore the appropriate query to change 'XXXXXXXXXX' to '(XXX)XXX-XXXX' is:

update [TableName]

set [phone] = '('+substring([phone],1,3)+')'+substring([phone],4,3)+'-'+substring([phone],7,4)

where len([phone]) = 10

The qualifier (where len([phone]) = 10) will prevent altering any record with more than ten characters - which ensures your phone numbers won't be contaminated if you accidentally run the query twice in a row.



Related Topics



Leave a reply



Submit