Script to correct data of incorrect format

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Script to correct data of incorrect format

Author

Topic

Sparxx
Starting Member

23 Posts

Posted - 2009-03-31 : 11:50:26

Currently I am trying to determine a way to run a script that will search a database for a field that contains incorrectly formatted data.

For example, I have fields that contain invalid formatted data for money values. The field they fall in are VARCHAR, not NUMERIC, to accept the placement of a decimal. A dollar amount field format is supposed to be a sort of NNN.NN format. Now, the length limit of each dollar field is 10 characters; so essentially the field could have a max length of NNNNNNN.NN.

Through other monetary transactions, the fields are accessed quite frequently and through some calculations (and estimations) some dollar fields end up as NNN. NN.NNN or NN.N or .NNNN . I understand that due to the field data type being VARCHAR, that this would technically pass as valid. However, I have a reporting program that runs on this database looking at dollar values such that it scans for the standard NNN.NN format and generates a report of dollar amounts when complete. The program crashes when it comes to the "invalid" dollar values.

I am unable to rewrite the program that reports this data to "guestimate" the total. Also, this happens to a very small amount of dollar fields so they are hard to root out by doing a manual search line-by-line, field-by-field.

Is there a script that can be generated to narrow down all offenders of the dollar format so that I can edit them line by line after they have been found?

If you need more information, I will try my best to supply it.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 11:53:54

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90279&SearchTerms=isnumeric

Sparxx
Starting Member

23 Posts

Posted - 2009-03-31 : 12:30:22

Ok, I have created the utility in the database.

Now, I am not sure how to run it to check against my dollar fields.

Forgive my ignorance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 12:51:07

[code]select * from your TableName where dbo.kk_fn_UTIL_IsMoney(ColumnName) is null[/code]

Sparxx
Starting Member

23 Posts

Posted - 2009-03-31 : 13:09:39

I run that against a test database I had set up with "invalid" dollar values.
I run it and no results show - not even a '0 rows affected' message.

I have run a script on the test database to affect 533 entries. So, in theory, it should pick up those 533 invalid formats. The format I set up was 123.456, and the script found none of them.

Am I running the script wrong?

(Yes, I replaced 'your TableName' with my naming scheme for the table I am checking; I did the same with 'ColumnName')

dsindo
Starting Member

45 Posts

Posted - 2009-03-31 : 14:56:47

do a select * from urtable
where isnumeric(columnname) =0

then insert a replace(columnname,...) on a case to case basis

Sparxx
Starting Member

23 Posts

Posted - 2009-03-31 : 15:08:02

I think this is slightly skewing from my main request. Then again, I may have not explained it properly. I have a problem with that (where it makes sense in my head but the listener/reader goes "huh?"

)

Basically I need a script that can find the "invalid" number formats (i.e. like 123.456, or 123.4, or .123, or 123.) in which I can then run a script one by one to correct each invalid dollar amount

shaunc
Starting Member

28 Posts

Posted - 2009-04-01 : 11:52:00

If I'm understanding correctly, this should find the rows you want to identify:

SELECT * FROM yourTable
WHERE PATINDEX('%_.__', yourColumn) = 0

Sparxx
Starting Member

23 Posts

Posted - 2009-04-01 : 14:47:45

WOW ... that is perfect.

I ran several tests w/ different values and each time the script found every instance of invalid formats. I am very pleased with this script. THANK YOU SO MUCH!!!

Now, searching the forums on correcting these formats, I found this [url]http://www.sqlteam.com/forums/post.asp?method=Reply&TOPIC_ID=103032&FORUM_ID=30[/url]. Running this script rounds the decimal places onscreen, but does not actually change the data in the database. Is there a way to manufacture a script to combine update and the round script to quickly correct data; short of manually changing it field by field?

Additionally, there is occasionally the time where the field is empty - either due to it being newly created or that it has been cleared due to inactivity. In any case, is there a way to use this script to exclude empty fields?

dsindo
Starting Member

45 Posts

Posted - 2009-04-01 : 16:34:08

where columnname is not null?

Sparxx
Starting Member

23 Posts

Posted - 2009-04-01 : 19:38:32

Correct, just empty - no data present.

To make it a little clearer, the database contains Patient Claims and the fields for these claims have been set up as VARCHAR and not MONEY or NUMERIC (I suppose this was to allow a decimal point). Through a third-party process, secondary claims are formed after the primary insurance carrier pays on the claim, leaving an amount left in the field after doing a couple of mathematical calculations to determine what is owed of the patient or secondary insurance. Sometimes these calculations do not round (or estimate) the decimal, leaving an obscure format behind. This later crashes a reporting module that tries to calculate all of the available charges in any live claim we have in our system.

If the fields had NULLs in them, then our software would crash trying to interface with the claim. Our software sits between a medical biller and the database as a visual representation of what the claim would look like if it were to be printed.

So, to make a long story short - those money fields are empty if no mathematical calculations are performed on those fields.

Sorry for the long-winded explanation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-02 : 05:17:55

Note that isnumeric() is not always reliable
select isnumeric('.'),isnumeric('.'),isnumeric('12d1')

Madhivanan

Failing to plan is Planning to fail

Sparxx
Starting Member

23 Posts

Posted - 2009-04-02 : 06:46:10

I'm not sure what that does? Is it supposed to be run in conjunction w/ another script? I ran it for my table and received the following:
1 1 1
and it showed up 533 times - it doesn't show me any formatting issues with the fields in question.

Sparxx
Starting Member

23 Posts

Posted - 2009-04-02 : 10:37:28

I have been able to isolate entries that have no data in the field by doing the following (and using the script from DSINDO):
SELECT * FROM Table1
WHERE PATINDEX('%_.__', FieldName1) = 0 and FieldName1 = ' '

Now is there a way to utilize that isolation by excluding or ommitting that data from the search? As in, "search for this criteria, but exclude this criteria"....

Sparxx
Starting Member

23 Posts

Posted - 2009-04-02 : 12:28:09

Ok ... I think I got it this time - and please correct me or feel free to help with any improvements this may need:

USE [DB1]
SELECT * from [Table1]
WHERE PATINDEX('%_.__', [Field1]) = 0 and isnumeric([Field1]) > 0

This seems to work! It first pulls out all the data that is not formatted correctly, then follows up with clearing out blank fields (using the > 0 for isnumeric() ).

WOW!

Manufacturing scripts is fun! LOL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 02:50:17

quote:
Originally posted by Sparxx

Ok ... I think I got it this time - and please correct me or feel free to help with any improvements this may need:

USE [DB1]
SELECT * from [Table1]
WHERE PATINDEX('%_.__', [Field1]) = 0 and isnumeric([Field1]) > 0

This seems to work! It first pulls out all the data that is not formatted correctly, then follows up with clearing out blank fields (using the > 0 for isnumeric() ).

WOW!

Manufacturing scripts is fun! LOL

As I pointed out, isnumeric is not reliable

See this example

declare @t table([Field1] varchar(10))
insert into @t
select '23.666' as [Field1] union all
select '223.666' as [Field1] union all
select '3.66' as [Field1] union all
select '1,1' as [Field1] union all
select '.' as [Field1]

--Your code
SELECT * from @t
WHERE PATINDEX('%_.__', [Field1]) = 0 and isnumeric([Field1]) > 0

--My suggestion
SELECT * from @t
WHERE [Field1] like '%[0-9][.][0-9][0-9][0-9]'

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-04-03 : 10:03:18

quote:
Originally posted by Sparxx

...the fields for these claims have been set up as VARCHAR and not MONEY or NUMERIC (I suppose this was to allow a decimal point).

You suppose wrong. MONEY and NUMERIC can, of course, handled decimal points.
The correct answer was: "The original application developer was an idiot".
Change the datatype to money, if at all possible.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Sparxx
Starting Member

23 Posts

Posted - 2009-04-03 : 12:17:49

LOL

Yes, I would definitely concur. I know the guy who developed the database structure for our software and know he can be a jerk sometimes .... and, as stated above (

), an idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources