Oh, is it really such a bad design? Here is some more. What is substring matches, and why do i need them?
TBL_PRODUCTS ID PRODUCTNAME 1 SAMSUNG TV 2 PHILIPS DVD-PLAYER 3 PHILIPS TV 4 MAXTOR DMAX 5 LaCie HARDDIVE --------------------------------------------- TBL_SPECS ID DETAIL 1 Widescreen 2 VCD 3 DiVX 4 Capacity 5 Inch --------------------------------------------- PRODUCT_SPECS PRODID SPECID VALUE 1 1 YES 1 5 32 2 2 NO 2 3 3.11 3 1 NO 3 1 28 4 4 80 5 4 120 ----------------------------------------- Thanks again for your help! Quoting [EMAIL PROTECTED]: > 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 > ------------------------------------------------- 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]
