Eliminating invalidly formatted decimal text strings

• MattieNH

  SSCertifiable

  Points: 7463

  More actions

  January 19, 2010 at 8:54 am

  #209243

  I have the following code I need to modify.

  UPDATE StatCasesSentencesAmounts

  SET DispComponentFineAmount = replace(rtrim(ltrim(cast(dcv1.FieldValueas varchar(500)) )), ',', '')

  FROMJustice.dbo.DispComponentInstdci

  INNER JOIN StatCasesSentencesAmountsscsa

  ON dci.ContainerID = scsa.SentenceID

  ANDdci.DispositionComponentInstanceID= scsa.DispositionComponentInstanceID

  INNER JOIN DispComponentVal dcv1

  ON dci.DispositionComponentInstanceID= dcv1.DispositionComponentInstanceID

  WHERE rtrim(ltrim(cast(dcv1.FieldValueas varchar(500)) )) <> ''

  AND REPLACE(replace(rtrim(ltrim(cast(dcv1.FieldValue as varchar(500)) )), ',', ''), '.', '') NOT LIKE '%[^0-9]%'

  AND dcv1.FieldName = 'mskNHFEEFines'

  DispComponentFineAmount (the field being updated) is a decimal(20,2) field. dcv1.FieldValue is a text field. DispComponentVal is a table with entity/attribute/value fields, which allow free form data entry of what are supposed to be numeric amounts.

  The first two lines of the where clause are attempting to weed out blank fields (which is perfectly acceptable, given that not everyone pays a fine), and fields that contain something other than a number. Replacing the commas and decimal points when doing that test has until recently worked fine in weeding out values that couldn't be stored in a decimal field.

  Then someone decided to enter the value 620.. in the FieldValue field, and this logic can't handle the double decimal point. I can't ignore the decimal point in these numbers, because that might inflate the fine amount.

  So I need a way to skip over these values as well. This is a SQL Server 2008 database, so I'm thinking that maybe there can be some use of regular expressions to do this. But I don't speak 'regular expression' (and find them just about impossible to read). So does anyone have a suggestion on how to limit updating to values that match what Americans consider a validly formatted dollar amount?

  Thanks,

  Mattie

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  January 19, 2010 at 9:39 am

  #1105522

  Do not know if this will help you, but have you examined CHARINDEX function? For example:

  DECLARE @dcv1_FieldValue VARCHAR(10)

  DECLARE @N INT

  DECLARE @Next INT

  SELECT @dcv1_FieldValue = '620..00'

  SET @N = CHARINDEX('.',@dcv1_FieldValue,1)

  SELECT @N

  SET @Next = CHARINDEX('.',@dcv1_FieldValue,@N+1)

  SELECT @Next

  IF @Next < @N PRINT 'ok.'

  ELSE PRINT 'oops.'

  Hope this gives you an idea to use CHARINDEX in a more efficient manner than my sample.

  Something bothered me after this posting ... if your field is a TEXT data type then do someting similiar using PATINDEX .

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• jcdyntek

  Ten Centuries

  Points: 1153

  More actions

  January 19, 2010 at 12:21 pm

  #1105661

  you can use the ISNUMERIC function to determine if the string is a valid number and do the processing on those and create exceptions for the ones that are not numeric to process by hand

  select ISNUMERIC('12..022') will return 0, valid numbers return 1.

  http://msdn.microsoft.com/en-us/library/ms186272.aspx

• Jeff Moden

  SSC Guru

  Points: 1004561

  More actions

  January 19, 2010 at 10:45 pm

  #1105840

  You can't use ISNUMERIC alone because things like '2e3' and '2d3' are valid "convertable" numbers according to ISNUMERIC so be sure to also include whatever other logic you already have in place for alpha-detection and rejection.

  If you don't have such detection/rejection already built in and need help doing so, post back.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)