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 |
|
Stellafish
Starting Member
2 Posts |
 Posted - 2009-01-28 : 09:14:35
|
| Hi All,I support a large application with a VB front end and a SQL backend.Recently, we have discovered that an older version of the app did not have any validation on a phone number field.The colume in the DB is not set to NUMERIC but unfortunately, due to red tape and the fact that the development team are based in Indai, I am unable to change this.However, in the latest version of the App, there is validation and it now only allows numeric characters and any spaces are removed before the data is committed to the DB.I'm now in a situation where I need to run a script to do a clean up of the existing phone numbers.The script I currently have looks for non numeric characters and and also looks for data where, once any spaces have been removed, there are only 11 characters eg a valid phone number format - 01234567890 and the script works great, The problem I'm having is that the full stop, minus and plus symbols are treated as numeric as long as they have a following number eg -1, or .2 or +5Is there any easy way, without tripling the amount of work the script has to do by using LIKE eg '%.%', that will allow me to exlude any rows from my script that contain these charaters.Basically, I only want my script to update rows that only have charcters 0-9 in them??? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-28 : 09:17:55
|
SELECT *FROM Table1WHERE Col1 LIKE '%[^0-9]%'to get all records where a character not in 0-9 exists.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Numeric Field Check