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
Splitting SQL Column into Multiple Columns Based on Value
Create a Query That Will Display Employee Name and Department Number
How to Add a Space Between Two Text in SQL Code
Sql: Select All Rows If Parameter Is Null, Else Only Select Matching Rows
Sql Server: Create an Incremental Counter for Records in the Same Year
Formatting Numbers by Padding With Leading Zeros in SQL Server
How to Use Select Distinct and Concat in the Same SQL Statement
Convert String to Date in Ms Access Query
Mysql Error 1264: Out of Range Value for Column
Check If Column Exists Before Executing Oracle
How to Combine Two Completely Different SQL Queries into One Result
Query: Fetch 3 Records Which Has Higher Value
Checking If a SQL Server Login Already Exists
How to Create Column in SQL Query With Custom Text
Sql - Select Parent and Child Records in an Order
Add a Column to Specific Position in Mssql Server