Can't Query a dinosaur Database with ASP

[Rexolio]

Hi everyone,

Got a SQL Server 2000 database table that has been around for ages with thousands of records in them. There is a field that holds budget information. Most of the time all entries go in like this:

10000 is entered for $10,000
15000 is entered for $15,000

However, the field type is not currency or number because the database is so old and has information on clients entered from years ago where some of the entries, instead of being 10000 for $10,000, may have been entered "10k" or even worse, "don't know" and other entries like this.

Therefore, the field type is Nvarchar.

So, we have form that clients can query and I'm trying to set it up where they can use budget as a filter. However, when someone queries something like:

budget > 5000 and budget < 20000 I get the following type of entries:

6000
600
7500
8000
800
2000
20000

Not really sure how to resolve this! It would take tons of time to go through and clean up the database in order to change the file type.

Thanks for your time.


rexolio@bellsouth.net
&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[LakshmiKiran]

Hi,
If you do the casting it seems to work fine. Check this...
select * from test where cast(budget as money) > 10

 

[sezme]

It seems to me that the only way you can end up with a non corrupt database would be to go through it and change the corrupt data / correct it - then you can change the file types.

Why not filter the recordset to show file that are not numeric - this will leave you with the fields you need to change - there may not be that many.

Just a thought

Sez

 

[Tarwn]

You could probably clear up most of the issues with 2 SQL Update statements:

1) UPDATE MyTable SET budget = SUBSTRING(budget, 1, charindex('k', budget) - 1) & '000' WHERE budget LIKE '%k'

2) UPDATE MyTable SET budget = '-1' WHERE IsNumeric(budget) = False

You may want to test those out in a test environment before hand, but I think that will pretty much convert everything you need. Wasn't 100% on the ampersand in the first statement, I justr realized how infrequently I concatenate strings in SQLServer so the ampersand was a guess.


Basically this should clean up all of the non-numeric fields in the db and leave you with the capability to switch to a numeric type.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[Rexolio]

Thanks for the suggestion guys. PART of the problem is that we still get some new data from various clients that aren't entered correctly. If on the web, we have scripts to change the info, but still there's ways for it to get it entered incorrectly. We also get database entered manually via various databases and the same thing goes here... sometimes people will still enter something other than a strict number.

If we change the file type, it will start causing problems when merging databases. So we figured it would be best to leave that particular field as Nvarchar.

Still have open ears, though!


rexolio@bellsouth.net
&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[Tarwn]

Well, you could always do your comparison SQL selection using numeric casts. Right now it ppeaers to be using alphabetic precedence to decide for the less than and greater than cases, if you do on-the-fly casts to numbers you may be able to get around the &quot;i don't know's&quot;. As far as the 10k's you will still need to do a conversion on them because it will be even nastier if you have to do an on-the-fly conversion in every select statement to clear those up.

I would suggest create a stored procedure with the first update above in it and just run it occasionally. May be a pain in the --- but it will allow those entries to show up in recordsets without having to create a really innefficient procedure to convert them on the fly during every select statement.

As far as your select goes it is going to be fairly complicated since you can't do a straight cast to a numeric type (it will throw errors on the text ones like I Don't Know). It may be easier to handle this with an internal select statement, but I'll leave it up to the real SQL authors to try this one.

-T



[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/