Extracting first Three number for a Mobile Prefix

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting first Three number for a Mobile Prefix

Author

Topic

ultraviolet2006
Starting Member

6 Posts

Posted - 2008-09-10 : 05:57:52

Hi Guys

This i My First Post, and i Need a Help from Professional Guys like you.

i need to Extract from a Column of Mobile Numbers, the first three or Four Numbers.

as Example:

if the Mobile number start with 965xxxxxxxxx

i need a Query to extract me the Number 965 from the Column Mobile number and tell me its final Count

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 06:02:35

use LEFT(mobile_number, 3)

what is final count ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 06:04:05

Final count of first three digits?
To do a count, you don't need to take LEFT of a column.
Just count all records. It's easier, unless you haven't told us thw whole story?

SELECT COUNT(*)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 06:06:56

maybe this


select left(mobile_number, 3), count(*)
from   yourtable
group by left(mobile_number, 3)

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 06:09:52

Ok. I see.
That's one definition I didn't know about "final count".

E 12°55'05.63"
N 56°04'39.26"

ultraviolet2006
Starting Member

6 Posts

Posted - 2008-09-10 : 06:32:06

Guys Thanxs for the Fast Replies,

I will clear my Request Again:

say i Have a Table name msisdn, and it has variety of mobile numbers, i need to extract in that column the Mobile number that only start with 965, and let it Count the Total of that Result, and i need to search in a certain Period of Time, say from 1 August 2008 to 1 Sep. 2008

as example below:

Msisdn|
------|
963054|
201058|
854239|
963787|

The Result:

Msisdn|Counts
963 | 2

ultraviolet2006
Starting Member

6 Posts

Posted - 2008-09-10 : 06:55:48

Any Reply Guys ??

aprichard
Yak Posting Veteran

62 Posts

Posted - 2008-09-10 : 07:24:43

This may be u expected

select left(mobile_number, 3), count(*)
from yourtable
where mobile_number like '965%'
group by left(mobile_number, 3)

ultraviolet2006
Starting Member

6 Posts

Posted - 2008-09-10 : 07:35:50

quote:
Originally posted by aprichard

This may be u expected

select left(mobile_number, 3), count(*)
from yourtable
where mobile_number like '965%'
group by left(mobile_number, 3)

Thanxs man that one Works Really Good but can you please tell me how to search in a Specific period of time, where i have a Column for (Date and Time) in the Same Table i Search.

ultraviolet2006
Starting Member

6 Posts

Posted - 2008-09-10 : 07:59:37

Thanxs man that one Works Really Good but can you please tell me how to search in a Specific period of time, where i have a Column for (Date and Time) in the Same Table i Search.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 08:10:07

[code]select left(mobile_number, 3), count(*)
from yourtable
where mobile_number like '965%'
and datecol >= @start_date
and datecol < dateadd(day, 1, @end_date)
group by left(mobile_number, 3)[/code]

KH
[spoiler]Time is always against us[/spoiler]

ultraviolet2006
Starting Member

6 Posts

Posted - 2008-09-10 : 08:40:10

Thanxs That works, Thanxs For helping Me, keep it UP!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 09:17:58

select '965', count(*)
from yourtable
where mobile_number like '965%'
and datecol >= @start_date
and datecol < dateadd(day, 1, @end_date)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources