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 |
db_sysadmin
Starting Member
27 Posts |
Posted - 2006-01-06 : 08:58:38
|
Hey Guys,Is there a way to do something like this:SELECT * FROM Table WHERE isnumeric(varcharfield)What I mean is to select rows that are numeric on a varchar type column.Thanks in advance... |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-01-06 : 09:01:10
|
depends what you mean by numeric.SELECT * FROM Table WHERE isnumeric(varcharfield) = 1will allow through things like 1e2 which can be converted to a numeric datatype.You might wantwhere patindex('%[^0-9]%',varcharfield) = 0which will give those which have only numeric digits.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
db_sysadmin
Starting Member
27 Posts |
Posted - 2006-01-06 : 09:10:32
|
Thanks for your reply...What I want to do is...convert a nvarchar field to numeric...so I want to see which records contain numeric values to correct the ones that are not...I couln't do it yet...I keep getting this:- Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to numeric.seems to be a problem with Null and blank ocurrences...Thanks a lot!!! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-06 : 09:21:08
|
Did you try nr's query?MadhivananFailing to plan is Planning to fail |
|
|
db_sysadmin
Starting Member
27 Posts |
Posted - 2006-01-06 : 09:27:53
|
I did, thank you both guys!,I worked it out...it was a problem convertin an empty string to NULL.Thansk... |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-06 : 09:35:50
|
Seems like you are all done ... but in case you've got more to do! :I've got a function that converts varchar to int - and returns NULL for failures - which is a bit more robust than IsNumeric()http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59049Kristen |
|
|
|
|
|