How do you separate phone number and cellnumber that are in one column?

Question

1.00/5 (2 votes)

See more:

Cell numbers and Phone numbers are in one column and they are in different format eg(021-5454456,0213434559, 021 3469564) and cell numbers are in this format(0784595984,078 7894836). I want to display only cell numbers.

Posted 29-Oct-15 9:07am

Avuy

Add a Solution

Comments

Maciej Los 29-Oct-15 17:20pm

If cell numbers starts with some numbers, there's a chance to filter only cell numbers.

3 solutions

Solution 3

Thank you so much i used like this: select * from tblName where col not like '021%'

Posted 29-Oct-15 21:37pm

Avuy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2015-10-29T10:14:00

Solution 2

Regulat expression s might help you.
Unfortunatelly (among others) regexp is not built in in sql server, but you can have it:
xp_pcre - Regular Expressions in T-SQL[^]
Regular Expressions in MS SQL Server 2005/2008[^]

You will also need to split the values in that field that match with the cell phone pattern, whatever that would be. This can be also done: http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html[^]

Still it looks better to either create a proper CLR function[^] that does the splitting and the filtering and it is returning only the desired values as table (even formatted identically) - or to move this functionality in an upper layes, as SA suggested.

Posted 29-Oct-15 10:14am

Zoltán Zörgő

Comments

Sergey Alexandrovich Kryukov 29-Oct-15 16:23pm

Well, this is useful information, and a good point on "CLR function", a 5. Still, look at the discussion in comments to Solution 1, which is, according to the inquirer, is inapplicable, but this is the root of the problem.
—SA

Zoltán Zörgő 29-Oct-15 17:04pm

Thank you.
The only statement OP gave is that the database is not created by him/her. It still might be possible to add extra functionality, like a CLR UDF. It is a completely other thing than altering the schema - as that is much likely bound by the application above.

Sergey Alexandrovich Kryukov 29-Oct-15 17:18pm

Good point, but her/his statement is that schema modification is not possible; god bless them. :-)
So then the usage is bound to application level...
—SA

Sergey Alexandrovich Kryukov · Accepted Answer · 2015-10-29T09:15:00

Solution 1

It simply means that you have chosen extremely bad database schema. Instead of separating these strings, you should have organized storage of them as separate records. For example, you could have a table of phone numbers with the field showing its type (mobile, home, etc.), or (worse) have fixed number of phones as attributes of the person or organization. Something like that.

—SA

Posted 29-Oct-15 9:15am

Sergey Alexandrovich Kryukov

Comments

Avuy 29-Oct-15 15:19pm

I understandd and i agree, but the database was not created by me, I need to create a report displaying only the cell numbers

Sergey Alexandrovich Kryukov 29-Oct-15 15:24pm

Sorry for you. Then you could split the number in your UI, data adapter or BL layer, whatever is that.
—SA

Matt T Heffron 29-Oct-15 16:12pm

If your examples above are represenatative, then cell numbers have only a single comma while the other phone numbers have two. You may be able to use that fact to distinguish.

Sergey Alexandrovich Kryukov 29-Oct-15 16:20pm

That's right. But "if representative" is the key here. If, by schema, there are no constraints on the string format in the schema (and it's nearly obvious that there is no such thing), one would need to deal with any possible garbage in this field. :-)
—SA