| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2005-07-14 : 05:35:43
|
| Hi,How do you identify if a character (other than a number) is present in the field?Thanks |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-07-14 : 06:03:11
|
| If you replace all number characters with 'empty strings' and the length of the amended string is > 0....then you've got a non-number character in it |
 |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-07-14 : 06:58:44
|
| IsNumeric(replace(field, ' ', '')) = 0? |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-14 : 07:07:01
|
| Hi,I don't think I should be doing this because the field is holding phone numbers. some of these values have a bracket or a +So I just want to select those records with their values...Thanks |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-07-14 : 07:12:31
|
| ok, then replace these characters too or use Andrew's solution. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-14 : 07:47:14
|
| Great.Thank you all. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-14 : 07:50:38
|
quote:
Originally posted by AndrewMurphy
If you replace all number characters with 'empty strings' and the length of the amended string is > 0....then you've got a non-number character in it
How do I replace all number charactersI don't think I should just usereplace(field, 0, '')replace(field, 1, '')replace(field, 2, '')replace(field, 3, '')replace(field, 4, '')... |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-07-14 : 08:03:14
|
| e.g. isNumeric(replace(replace(field, ' ', ''), '-', '')) = 0 |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-14 : 08:29:55
|
| -- Rows with a nonnumeric characterSELECT <col> FROM <tbl>WHERE PATINDEX('%[^0-9]%',<col>) > 0rockmoose |
|
|
|
|
identify char