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 |
|
hitori
Starting Member
3 Posts |
 Posted - 2001-12-07 : 12:09:23
|
| I'm trying to uniquely identify a 10 digit number from a varchar(2000) field. The number is always different, (phonenumber) and other text etc. surrounds the number.And I'm trying to just extract the number..no unique identifyer is ever consistently before or after the number, thought of that...So here's an exampleField1-------abcdefghijklmnop see jimmy run. 1234567890 see sarah run she's pretty fast qrstuvwxyzWhat sort of syntax could I use to retrive just 1234567890?Any ideas?Many thanks in advance =)~BenEdited by - hitori on 12/07/2001 12:10:31 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2001-12-07 : 12:37:07
|
| 1 line of attack would be to search for a pattern of digits that matches a phone number....like 999-31-534-5356....start with looking for the 1st number (preceeded by a space?? or left bracket?)...and then continue to see if the next 10 chars fit the profile of being a candidate....if they do, then that's your answer....any possibility there's a 2nd number in the text?if the 10 don't match the profile/signature of a phone number....keep on searching until end of string.... |
 |
|
|
mono
Starting Member
36 Posts |
Posted - 2001-12-07 : 13:00:32
|
| This isn't a task for which SQL is really suited.If you have V2000, you could wirte a function that returns a comma separated list of ten digits numbers in a string passed to it.This sort of thing is a job for regular expressions and SQL server is totally lacking in regexp support. Textpipe pro (http://www.crystalsoftware.com.au/textpipe/engine.html) specialises filtering/trnasformations like that and it comes with a set of DLLs you could use to access it from SQL Server (I think; I've never used extended stored procs)That should get you most of the way there. |
|
|
|
hitori
Starting Member
3 Posts |
Posted - 2001-12-07 : 13:03:22
|
| Ty much guys, ya I know it's not relaly suited for this heh, trying to get this done asap you know how the boss is ;PThat helps lots thank you :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-07 : 13:10:21
|
| If it's just 10 consecutive digits, with no extra characters between digits, this might work:SELECT SubString(field1, PatIndex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', field1), 10) AS PhoneNumber FROM myTableDon't expect this to perform quickly. Like mono suggests, you are better off using a program with a regular expression parser to get the numbers out. And if you can control the data entry, you should set up another column to store phone numbers and require that they be entered there instead.Edited by - robvolk on 12/07/2001 13:10:55 |
|
|
|
hitori
Starting Member
3 Posts |
Posted - 2001-12-07 : 15:50:28
|
quote:
If it's just 10 consecutive digits, with no extra characters between digits, this might work:SELECT SubString(field1, PatIndex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', field1), 10) AS PhoneNumber FROM myTableDon't expect this to perform quickly. Like mono suggests, you are better off using a program with a regular expression parser to get the numbers out. And if you can control the data entry, you should set up another column to store phone numbers and require that they be entered there instead.Edited by - robvolk on 12/07/2001 13:10:55
Dude You rule 8) it worked my sincere thanks!!B |
|
|
|
|
|
|
|
|
Extract 10 digit value from huge varchar cell?