delete right characters

• Joseph Mizrahi

  Valued Member

  Points: 72

  More actions

  March 30, 2005 at 12:38 pm

  #164154

  I need some assistance...

  I have a column of numbers, in varchar format (I made a mistake, should have

  defined as numeric) but to further compound the problem, users have entered

  amounts, in this field, for example:

  123

  123.00

  123.12

  12345

  12345.

  123456.77

  ... etc

  What I Would like to do is to Trim everything to the right including the

  decimal pt. and leave those entries without (e.g. 123) decimal points, as is.

  Can someone please provide the syntax....

  Thanks !

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  March 30, 2005 at 12:55 pm

  #549635

  You will need to figure out how you want to loop, but this should work. 

  SELECT CONVERT( integer, LEFT( '123.45', (CHARINDEX( CHAR(46), '123.45') - 1)))

   

  Try this with out the CONVERT( integer....) and the -1 and you will see why the -1 needs to be included....

   

  I wasn't born stupid - I had to study.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  March 30, 2005 at 12:56 pm

  #549636

  You don't actually need to do that, you can simply add a new column of the correct datatype and do something like this :

  make sure that this statement returns nothing before running the rest :

  Select * from dbo.TableName where ISNUMERIC(OldCol) = 0

  alter table TableName

  ADD NewCol Decimal (18,4) null

  GO

  update dbo.TableName set NewCol = CAST(OldCOL as decimal(18,4))

  Select OldCol, NewCol from dbo.TableName

  go

  --only if you don't want to allow nulls

  ALTER TABLE TableName

  ALTER column NewCol decimal(18,4) not null

  GO

  --make sure that the application is updated before running this one (to allow for the new datatype of the column)

  alter table TableName

  drop column OldCol

  GO

  exec sp_rename 'dbo.TableName.NewCol', 'OldCol', 'COLUMN'

• ron k

  SSCertifiable

  Points: 6103

  More actions

  March 30, 2005 at 4:14 pm

  #549689

  From BOL this may help:

  ---

  FLOOR (T-SQL)

  Returns the largest integer less than or equal to the given numeric expression.

  Syntax

  FLOOR(numeric_expression)

  ---

  SELECT FLOOR(123.45) returns 123

   

• vadba

  SSChampion

  Points: 11133

  More actions

  March 31, 2005 at 8:05 am

  #549835

  Do you want to just truncate the fractional part, or round it (i.e. 123.4 becomes 123, while 123.8 becomes 124) ?

  Here's an example using elements from both responses:

  CREATE TABLE #temp

  (

    id int PRIMARY KEY IDENTITY(1,1)

  , vcData varchar(15)

  , intData int

  )

  SET NOCOUNT ON

  INSERT #temp (vcData) VALUES ('123')

  INSERT #temp (vcData) VALUES ('123.00')

  INSERT #temp (vcData) VALUES ('123.12')

  INSERT #temp (vcData) VALUES ('12345')

  INSERT #temp (vcData) VALUES ('12345.')

  INSERT #temp (vcData) VALUES ('123456.77')

  SELECT * FROM #temp

  -- This step if you want to round the values to the nearest whole number

  -- and store in a new int column.

  UPDATE #temp SET intData = CONVERT(int, Round( CONVERT( decimal(18,4), vcData), 0))

  -- Or, keep the varchar column, remove all decimal points, and ROUND the values

  UPDATE #temp SET vcData = CONVERT(varchar(15), CONVERT(int, Round( CONVERT( decimal(18,4), vcData), 0)))

  -- Or, keep the varchar column, remove all decimal points, and TRUNCATE the decimals

  -- Comment out the above "UPDATE #temp SET vcData =" statement, uncomment the one below

  -- UPDATE #temp SET vcData = CONVERT(varchar(15), CONVERT(int, CONVERT( decimal(18,4), vcData), 0))

  SET NOCOUNT OFF

  SELECT * FROM #temp

  DROP TABLE #temp