Click here to Skip to main content
15,923,142 members
Please Sign up or sign in to vote.
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
Comments
Maciej Los 29-Oct-15 17:20pm    
If cell numbers starts with some numbers, there's a chance to filter only cell numbers.

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.
 
Share this answer
 
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
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
 
Share this answer
 
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
Thank you so much i used like this: select * from tblName where col not like '021%'
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900