This sounds like a simple case of bad design. You need to be able to locate specific values for various product descriptions but they are all mangled together into just one field. You end up trying to do substring matches and all hell breaks loose and performance hits the skids.
My suggestion is to somehow re-process your "value" column into separate specific columns or child tables, one for each distinct value held in the "value" field. I can identify the potential values of `hdd_size`, `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format`.... Your data is unmanageable in its present format and you need to scrub and massage it into shape before what you have will be marginally useful. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER <[EMAIL PROTECTED]> wrote on 12/09/2005 04:56:21 PM: > 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 supportsDiVX 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
