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 |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2007-10-18 : 10:02:51
|
| Hi All,How to know if a varchar type is numeric or string?Let say I have a table called [TABLE1] which has a filed called [ID] of varchar type.Let say [ID] has following kind of records.100A23198765442A786B5987632103A756U5As you can see in above example there are two numeric and three string records. Is there any function which can tell me which records are numeric and which one is string?Looking for a quick solution.Thanks a million in advance.Zee |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-18 : 10:09:54
|
| this will get all varcharsselect * from WHERE yourColumn LIKE '%[^0-9]%'_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
bg_elliott
Starting Member
2 Posts |
Posted - 2007-10-18 : 10:10:23
|
| I think what you want is the T-SQL function "isNumeric". From the SQL Server documentation:ISNUMERIC ( expression ) Argumentsexpression Is the expression to be evaluated.Return TypesintRemarksISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 indicates that expression can be converted to at least one of the numeric types. |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2007-10-18 : 10:11:51
|
| Thanks a lot guys :) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-18 : 10:29:50
|
| note that isnumeric will return true forSELECT isnumeric('123d45')SELECT isnumeric('123e45')_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2007-10-18 : 22:32:51
|
| SELECT isnumeric('123e45') - "e" is this exponential?SELECT isnumeric('123d45') - How about "d"?Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-19 : 02:42:30
|
| Add a dot if you want to allow floating point numbers (which IsNumeric() will give True for of course, so Spirit's is better if you just want Integers)select * fromWHERE yourColumn LIKE '%[^0-9.]%'Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-19 : 04:40:55
|
| Not only Isnumeric() has problem but Isdate() as wellSelect isdate(2007),isdate('2007'),isdate(2222-22-22),isdate('2222-22-22')MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
How to know if a varcharType is numeric or string?