| Author |
Topic |
|
token
Posting Yak Master
133 Posts |
 Posted - 2006-05-01 : 06:45:25
|
Hi all,I was wondering if anyone knew how to split a string up into individual words and then to be able to insert those words into different columns?Basically my supplier has decided to put the product name and its description in a single field. So it reads like the following:Ferrari 4005WLMi AMD Turion ML37 (2.0GHz, 1MB cache), Microsoft® Windows® XP Pro, 1024Mb (2*512MB) DDR333 RAM, 100GB 5,400rpm HDD, 15.4"WSXGA TFT Display, ATI MOBILITY™ RADEON® X700 128MB, Bluetooth, 5 in 1 Card ReaderBut I need to split the string up so that the "Ferrari 4005WMLi" bit is inserted into a column called ProductName. And I also need to split the rest of the string up and insert them into various columns called Feature 1, Feature 2, Feature 3 etc.So for example, the ProductName will be "Ferrari 4005WMLi" and Feature 1 will be "AMD Turion ML37" and Feature 2 will be "1024Mb (2*512) DDR333 RAM".I can't think of a way to do it.... Do you think I should phone the supplier and abuse them for a bit?  |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-05-01 : 06:58:37
|
| How do you know how many "tokens / words" are the Product name?Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-01 : 10:14:49
|
| Assuming there is some variability to the pattern needed to match the columns, then you would probably be better using an application language for the parsing bit, rather than SQL Server. Particularly an application language with decent regular expressions - although it is possible to use a COM object for the RegEx stuff directly from SQL Server.(I'm assuming that under certain circumstances there might be a Space in the product name, and that you might want to parse the stuff in brackets, and so on)Kristen |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-05-01 : 18:10:51
|
| Thanks for your replies guys. The produt name and its description don't appear to be in a fixed format. They are all just concatenated in one field. But I think it will be satisfactory to tell SQL Server to split the words up everytime it encounters a comma.I am using ColdFusion as my language to display my database information on my webpage. I'm really unsure how to go about it, especially because I am a newbie and am trying to learn everything 'on the go'. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-01 : 18:59:26
|
Tell you supplier that they better give you the information in a usable format, because you found a number of other suppliers that are willing to do that, and you don't feel like doing a lot of work just to keep them.They may not go for it, but it doesn't hurt to try.  CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-02 : 03:50:58
|
| "But I think it will be satisfactory to tell SQL Server to split the words up everytime it encounters a comma"Then a SPLIT function will do the trick - see the link that Khtan postedKristen |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-06-17 : 16:07:19
|
quote:
Originally posted by Kristen
Assuming there is some variability to the pattern needed to match the columns, then you would probably be better using an application language for the parsing bit, rather than SQL Server. Particularly an application language with decent regular expressions - although it is possible to use a COM object for the RegEx stuff directly from SQL Server.
Hi Kristen, could you possibly expand upon what you mean here? What application language would you recommend or what do you use yourself?best wishes,Token |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-18 : 02:19:15
|
| You can use a SQL function to split a text string into separate rows, i.e. one row per element. In your case you would use COMMA as a delimiter and thus get individual "phrases" into separate rows.Once the elements are in a temporary table you can then utilise them more efficiently - element 2 will be your operating system, for example.As this is all done in SLQ its independent of the application language.The thing to get your head round is that this makes the operation Set Based, rather than using Loops and Sequential processing, and SQL is very efficient at Set Based operations - and generally rubbish at the loop/sequential stuff more normally used in application languages.Kristen |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2006-06-18 : 06:54:02
|
| But I've only just realised that splitting it up whenever a comma is ocurred is not enough. There is a lot of variability in the strings from the flat-files. Because there are 3 flat-files, each one from a different supplier.If I use the SPLIT function and have one row per word in a temp table, I then have to manually place these words into their corresponding columns in the permanent table?For over 20,000 products, I don't think I could manage that. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-18 : 12:40:21
|
| You are basically dealing with a crap, unformated, data source. If that's all you've got to work with then you are stuck with the solutions.Read on on the articles on Splitting Functions in SQL. You certainly don't want to split out each word, but once you have each "element" then applying them to your tables is easy, and very efficient in SQL.Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-18 : 14:07:26
|
quote:
Originally posted by Kristen
"But I think it will be satisfactory to tell SQL Server to split the words up everytime it encounters a comma"Then a SPLIT function will do the trick - see the link that Khtan postedKristen
Nope! See 5,400 rpmPeter LarssonHelsingborg, Sweden |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-06-18 : 23:32:16
|
| If you did not specify any format, chances are, the supplier just filled in the gaps :). try specifying a data format that you need and send that to your supplier, tell them that this is the format you want the data and the fields that you need. If comma is commonly used, try another character, like ~ or *. If you're supplier is concerned about your well being (that is, the bottomline your business contributes), they may work with a suitable solution to your needs. Just be sure you know what you need ;).May the Almighty God bless us all! |
|
|
|
|
Splitting up a string into individual words