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 |
|
silverheli2000
Starting Member
2 Posts |
 Posted - 2012-06-07 : 12:41:24
|
| Hi All!New here and need a little help. I am trying to identify records in a table that do not match a specific format. The field I am looking at is a phone number field, and I need to identify records not matching the format "xxx-xxx-xxxx" and that is not null. ANY advice would be greatly helpful...I've searched quite a bit and have not found anything close to what I'm looking for. Thank you! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-07 : 12:46:18
|
| select *from tblwhere s not like '%[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%'or s is nullif you want any character then [0-9a-zA-Z ]==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
silverheli2000
Starting Member
2 Posts |
Posted - 2012-06-07 : 14:20:51
|
| Thank you for the reply! Unfortunately I couldnt get the above to work, but was able to get assistance from one on our internal team who provided me with the following which did work for my needs:AND NOT regexp_like(Trim(hm_phone_nbr), '^[[:digit:]]{3}-[[:digit:]]{3}-[[:digit:]]{4}$') |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-07 : 22:38:56
|
| were you using Oracle?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Identifying records not fitting a specific format