Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to.
value(varchar) 80 17" 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supports DiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a "select value from tbl where value >=80 order by value DESC" will give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: > I misunderstood, I thought you were looking for a way of converting your > > numbers-as-strings into a native numeric format. > > Please describe you situation better: What language are you using to > build > your application. Are you composing the SQL statement client-side or > server-side? What kind of SQL statement are you trying to execute? > > Your table structures (the output of SHOW CREATE TABLE ... works very > well) and some sample data would also help. > > Sorry for the confusion! > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 04:28:44 PM: > > > Hello, thanks for your help! > > I dont really get it :) > > > > You suggestion is to have a seperate column with the name numericvalue > > and > > insert userinput into that and add a zero, right? > > > > Could you explain more, why when how will this help me :) > > > > Quoting [EMAIL PROTECTED]: > > > > > Assuming that your text data is in the column `userinput` and you > want > > > the > > > integer values to be in the column `numericvalue`, this statement > will > > > > populate the `numericvalue` column all at once: > > > > > > UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; > > > > > > You are better off checking for type-correctness before you enter > data > > > > into the database than you are trying to correct it after the input. > > > > However, I have had to do just this kind of conversion on many > occasions > > > > > > (old data, bad batch inputs, text file bulk loads, etc.) so I know > > > techniques like this still have their place. > > > > > > Shawn Green > > > Database Administrator > > > Unimin Corporation - Spruce Pine > > > > > > Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 03:30:17 PM: > > > > > > > in an application i have written there is the need to do a search > from > > > > > > mysql > > > > using numbers that are stored in a varchar column. it is not > > > > possible to store > > > > only the results with numbers in a seperate column. > > > > so i was looking at CAST(), is this a big performance loss? is > > > theresome > > > way > > > > of benchmarking different queries easy? > > > > > ------------------------------------------------- FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
