Author |
Topic |
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-12-05 : 16:04:57
|
I have a table Test. With a field Item (navarchar type)Item has the following kind of records.Memphis - 405435 - Pack 4 - Not assignedMemphis - 406254 - 6 Pack - Not assignedCHARLOTTE - 401033 - Number of items - Not assignedMEMPHIS - 401273 - Pack 12 - 0607043XX4X9CHARLOTTE - 401273 - Pack 12 - 0609070XQ2X6CHARLOTTE - 401273 - Pack 12 - 0610070XQ1XXCharlotte - 400106 - Number of items - Not assignedCHARLOTTE - 400106 - Number of items - 0607040XQ2XXMEMPHIS - 404919 - Pack 12 - 0607043XX4X9The length of each record may be different.In my select statement I want to break each record in 4 different columns.That is for the following item:Memphis - 405435 - Pack 4 - Not assignedI should see the following (Each field/records are seperated by comma '):Column 1, Column 2, Column 3, Column 4Memphis,405435,Pack 4,Not assignedIn my result set I want to see.Item,Column 1, Column 2, Column 3, Column 4Memphis - 405435 - Pack 4 - Not assigned,Memphis,405435,Pack 4,Not assignedRemember I dont need any space or "-", I need the actuall values as listed above.Any quick help would be highly appreciated.Thanks a million in advance. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 16:12:26
|
This will work as long as there a no points in the content.-- prepare test datadeclare @test table (item varchar(1000))insert @testselect 'Memphis - 405435 - Pack 4 - Not assigned' union allselect 'Memphis - 406254 - 6 Pack - Not assigned' union allselect 'CHARLOTTE - 401033 - Number of items - Not assigned' union allselect 'MEMPHIS - 401273 - Pack 12 - 0607043XX4X9' union allselect 'CHARLOTTE - 401273 - Pack 12 - 0609070XQ2X6' union allselect 'CHARLOTTE - 401273 - Pack 12 - 0610070XQ1XX' union allselect 'Charlotte - 400106 - Number of items - Not assigned' union allselect 'CHARLOTTE - 400106 - Number of items - 0607040XQ2XX' union allselect 'MEMPHIS - 404919 - Pack 12 - 0607043XX4X9'-- do the workselect item, parsename(code, 4) [Column 1], parsename(code, 3) [Column 2], parsename(code, 2) [Column 3], parsename(code, 1) [Column 4]from ( select item, replace(item, ' - ', '.') code from @test ) d Peter LarssonHelsingborg, Sweden |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-05 : 16:16:48
|
here's one way without a ton of ugly string-parsing in sql:1. select replace(Item, ' - ', ',') from yourtable2. write the result to a file3. bcp the resuling file into a table with your 4 columns. good one Peso. forgot about parsename(). SqlSpec: a fast and comprehensive data dictionary generator for SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003 http://www.elsasoft.org |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-12-05 : 16:47:45
|
Thnk you the feedback.jezemine,This is what I did.select replace(Item, ' - ', ',') ItemModifiedinto #t from TestNow when I run the following: select * from #t Now for example this is what i am getting.ItemModifiedMemphis,405435,Pack 4,Not assigned MEMPHIS,401273,Pack 12,0607043XX4X9 CHARLOTTE,400106,Number of items,0607040XQ2XX Now how can I put the above into 4 different columns like this (Here I have put comma in the records to identfiy that they belong to different field/Columns):Column 1, Column 2, Column 3, Column 4Memphis,405435,Pack 4,Not assigned MEMPHIS,401273,Pack 12,0607043XX4X9 CHARLOTTE,400106,Number of items,0607040XQ2XXAny quick help would be highly appreciated.Thanks a million in advance. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-05 : 16:59:05
|
what I meant for you to do in step 2 was to save the output of the query in step 1 to a file. You can do this in QA with the "results to file" setting. this will create a csv on your system.you can then bcp the csv in with a cmd like this (from the cmd line):bcp MyDatabase.dbo.MyTableWithFourColumns in file.csv -t, -c -T -SMYSERVERhope that's clearer. SqlSpec: a fast and comprehensive data dictionary generator for SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003 http://www.elsasoft.org |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 16:59:06
|
Don't you even read the suggestion you get?Peter LarssonHelsingborg, Sweden |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-12-05 : 17:01:24
|
Rember I have another fields other than Item, which i also want to include in my select list. I dont know if BCP can work with that. I dont know how to use BCP?.Can someone please help on the above....Thanks a million.... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 17:04:43
|
Why do I even continue to bother?Have you tried my suggestion I gave to you almost an hour ago?Peter LarssonHelsingborg, Sweden |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-12-05 : 17:11:07
|
Peso,Thanks for your feedback, sorry was unable to reply you.But I have lots of records in the Test table. I dont want to insert each one of them one by one. I am looking at jezemine suggestion, but please feel free to comment.Thanks... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 17:17:05
|
The first part of the code is just to set up a proper environment to the suggestion to run on, as the comment in the code "PREPARE TEST DATA"THIS IS THE CODE YOU SHOULD RUN ON YOUR TABLE!-- do the workselect item, parsename(code, 4) [Column 1], parsename(code, 3) [Column 2], parsename(code, 2) [Column 3], parsename(code, 1) [Column 4]from ( select item, replace(item, ' - ', '.') code from <yourtablenamehere> ) d Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 17:18:30
|
After this, I think I really need a I'll bet that in a few minutes I get a response that the query will not run."Unknown object" or something...Peter LarssonHelsingborg, Sweden |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-05 : 17:26:43
|
heh, I think i just confused the issue, but I didn't see your post at the time i posted first. OP certainly ought to be able to sort it out from what's here already... SqlSpec: a fast and comprehensive data dictionary generator for SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003 http://www.elsasoft.org |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 17:31:38
|
It's not you.If OP really cared about his question he should read all answers. And test them. And hopefully learn from them.Peter LarssonHelsingborg, Sweden |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-12-05 : 17:38:48
|
No you dont have to bet :DIt worked. Actually I never read your suggestion correctly. I just glanced it, and looked at jezemine suggestion instead. You script is perfect. I am just curious to know how the parsname() is working. That is for example in parsename(code, 4) what does 4 identify????I am not good in SQL. I am still learning.Thanks for the help.... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 17:42:06
|
Originally, the PARSENAME function was intended to break down fully qualified object names like this "db1.dbo.table1.column1", but has found many other useful ways.The first parameter is the string to break down. The second parameter of the function is the part number, from right to left.PARSENAME('db1.dbo.table1.column1', 4) gives "db1".PARSENAME('db1.dbo.table1.column1', 3) gives "dbo".PARSENAME('db1.dbo.table1.column1', 2) gives "table1".PARSENAME('db1.dbo.table1.column1', 1) gives "column1".Peter LarssonHelsingborg, Sweden |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-05 : 17:43:51
|
try BOL for syntax on all system functions:http://msdn2.microsoft.com/en-US/library/ms188006.aspx SqlSpec: a fast and comprehensive data dictionary generator for SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003 http://www.elsasoft.org |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-05 : 17:44:11
|
Whenever in doubt for a keyword, just select the keyword and press SHIFT-F1 to jump to the Books Online helpfile.Peter LarssonHelsingborg, Sweden |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-12-05 : 17:50:32
|
quote: Originally posted by Peso Originally, the PARSENAME function was intended to break down fully qualified object names like this "db1.dbo.table1.column1", but has found many other useful ways.
I always thought it's original purpose was for parsing 4 part names, like Server.Database.Owner.Table.Anyway, I guess OP got lucky there were only 4 values in that column. parsename() won't work with more than that... SqlSpec: a fast and comprehensive data dictionary generator for SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003 http://www.elsasoft.org |
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-12-05 : 17:51:50
|
Peso,Thanks foe the explanantion. Earlier I looked F1 help, but it was not clear. You have explain it better. So the PARSENAME only work against "." Correct me if I am wrong.Thanks again.... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-06 : 01:34:27
|
aeeshan13: What do Books Online tell you?jezemine: You are right. I just wrote it from the top of my head. I guess I should have read Books Online myself... quote: Value Description 1 Object name 2 Owner name 3 Database name 4 Server name
If there were five or more parts in the sample data, I think I would have solved the problem with different strategy.Peter LarssonHelsingborg, Sweden |
|
|
|