SQL Server Forums - Access query conversion with isnumeric

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

General SQL Server Forums

New to SQL Server Programming

Access query conversion with isnumeric

New Topic

Reply to Topic

Printer Friendly

Author

Topic

madamvegas
Starting Member

USA
3 Posts

Posted - 05/10/2012 : 11:03:52

I have this access query that I need to convert to sql and not quite sure what to use.

UPDATE Attribute_Data
SET Attribute_Data.AttValue = Int([AttValue])
WHERE (((IsNumeric([AttValue]))=-1) AND ((Right([AttValue],2))=".0"))
OR (((IsNumeric([AttValue]))=-1) AND ((Right([AttValue],3))=".00"))
OR (((IsNumeric([AttValue]))=-1) AND ((Right([AttValue],4))=".000"))
OR (((IsNumeric([AttValue]))=-1) AND ((Right([AttValue],5))=".0000"))
OR (((IsNumeric([AttValue]))=-1) AND ((Right([AttValue],6))=".00000"))

Theresa Burk

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts

Posted - 05/10/2012 : 11:41:16

Something like this?

UPDATE Attribute_Data 
SET Attribute_Data.AttValue = CAST([AttValue] AS INT)
WHERE ISNUMERIC([AttValue]) = 1 AND FLOOR([AttValue]) = [AttValue]

Most people who work with relational databases such as SQL Server would advise against using entity-attribute type of data storage. So if you have an opportunity to redesign the tables that would be something to keep in mind.

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/10/2012 : 13:29:50

This may be helpful for you to reference:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.06 seconds.