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.
| Author |
Topic |
|
eddie
Starting Member
45 Posts |
 Posted - 2003-03-25 : 14:58:18
|
| I accidently posted this to the general forum..sorry.how can I write a query to only return the numeric characters in a column. For example, I have to look up the telephone numbers from a char field. SOme are stored as (430)908-0982 I only want the number 4309080982 as I have to check the length and if it is 10 then grab the first three as the area and the last as the phone.Thanks,Eddie |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-25 : 15:13:07
|
| You might write a User Defined Function to do the job. If you know the non numeric characters are "()-" and space, then a replace could get the job done.I'd suggest writing an update procedure to put the column into a standard format that's usable. If you can't update that column, create another column in standard format for searching. Depending on the number of rows, it could be a real performance boost not to do SELECT time character replacement.If you want to do it in the SELECT:SELECT *FROM MyTableWhere '4309080982' = REPLACE(REPLACE(REPLACE(REPLACE(PhoneNumber, '(',''), ')',''),'-',''),' ', '')If you are worried about leading and trailing blanks, use LTRIM AND RTRIM (see BOL).Messy. I'm hopeful someone will post a better solution. Sam |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-25 : 15:33:37
|
| http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=24867Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-25 : 16:00:17
|
| Eddie --if you can, in the future, consider storing your data in a more raw, consistent format. If you just store the digits it is easy to validate (length must be 10, etc.) and you can always format it at the client end easily, or by using a VIEW.If the numbers are stored consistently, then you know LEFT(phoneNumber,3) is always the area code, right(phoneNumber,4) is always the last 4 digits, etc ... saves a LOT of hassles.Better yet, break it up into 3 fields and just concatenate as needed.Just some ideas, but I suspect you are stuck working with existing data.- Jeff |
|
|
|
|
|
|
|
|
only selecting numerics from a char field.