Need to convert Access query to SQL

• tburk 5368

  Mr or Mrs. 500

  Points: 555

  More actions

  May 10, 2012 at 8:27 pm

  #257866

  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"))

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 11, 2012 at 8:54 am

  #1486363

  What is it trying to do? This can certainly be made quite a bit simpler by the looks of it.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  May 11, 2012 at 9:48 am

  #1486419

  tburk 5368 (5/10/2012)

  ---

  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"))

  update attribute_date set attvalue=convert(int,convert(decimal(20,5),attvalue))

  where isnumeric(attvalue)=1 and convert(int,convert(decimal(20,5),attvalue))=convert(decimal(20,5),attvalue)

  or something like that - i'm sure others will have better solutions

  MVDBA

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  May 11, 2012 at 1:28 pm

  #1486598

  Are you just trying to lop off the zeros to the right of the decimal place? If so, you could just convert to DECIMAL with 0 decimal places. It really should not matter if you take the extra step to cast or convert to int if the column is not an int column anyway. Try this.

  IF OBJECT_ID('#Attribute_Data','u') IS NOT NULL

  DROP TABLE #Attribute_Data

  GO

  CREATE TABLE #Attribute_Data

  (

  AttValue VARCHAR(20)

  )

  GO

  INSERT INTO #Attribute_Data

  SELECT '1.00000' UNION

  SELECT '2.0000' UNION

  SELECT '3.001' UNION

  SELECT 'x'

  SELECT * FROM #Attribute_Data

  UPDATE #Attribute_Data

  SET AttValue = CAST(AttValue AS DECIMAL(28,0))

  FROM #Attribute_Data

  WHERE ISNUMERIC(AttValue) = 1

  AND CAST(AttValue AS DECIMAL(28,0)) = CAST(AttValue AS DECIMAL(28,18))

  SELECT * FROM #Attribute_Data

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.