| Author |
 Topic  |
|
|
zeeshan13
Constraint Violating Yak Guru
USA
347 Posts |
 Posted - 12/05/2006 : 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 assigned
Memphis - 406254 - 6 Pack - Not assigned
CHARLOTTE - 401033 - Number of items - Not assigned
MEMPHIS - 401273 - Pack 12 - 0607043XX4X9
CHARLOTTE - 401273 - Pack 12 - 0609070XQ2X6
CHARLOTTE - 401273 - Pack 12 - 0610070XQ1XX
Charlotte - 400106 - Number of items - Not assigned
CHARLOTTE - 400106 - Number of items - 0607040XQ2XX
MEMPHIS - 404919 - Pack 12 - 0607043XX4X9
The 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 assigned
I should see the following (Each field/records are seperated by comma '):
Column 1, Column 2, Column 3, Column 4
Memphis,405435,Pack 4,Not assigned
In my result set I want to see.
Item,Column 1, Column 2, Column 3, Column 4
Memphis - 405435 - Pack 4 - Not assigned,Memphis,405435,Pack 4,Not assigned
Remember 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
Sweden
29138 Posts |
Posted - 12/05/2006 : 16:12:26
|
This will work as long as there a no points in the content.-- prepare test data
declare @test table (item varchar(1000))
insert @test
select 'Memphis - 405435 - Pack 4 - Not assigned' union all
select 'Memphis - 406254 - 6 Pack - Not assigned' union all
select 'CHARLOTTE - 401033 - Number of items - Not assigned' union all
select 'MEMPHIS - 401273 - Pack 12 - 0607043XX4X9' union all
select 'CHARLOTTE - 401273 - Pack 12 - 0609070XQ2X6' union all
select 'CHARLOTTE - 401273 - Pack 12 - 0610070XQ1XX' union all
select 'Charlotte - 400106 - Number of items - Not assigned' union all
select 'CHARLOTTE - 400106 - Number of items - 0607040XQ2XX' union all
select 'MEMPHIS - 404919 - Pack 12 - 0607043XX4X9'
-- do the work
select 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 12/05/2006 16:12:41 |
 |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 12/05/2006 : 16:16:48
|
here's one way without a ton of ugly string-parsing in sql:
1. select replace(Item, ' - ', ',') from yourtable
2. write the result to a file
3. 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 |
Edited by - jezemine on 12/05/2006 17:45:50 |
|
|
|
zeeshan13
Constraint Violating Yak Guru
USA
347 Posts |
Posted - 12/05/2006 : 16:47:45
|
Thnk you the feedback.
jezemine,
This is what I did.
select replace(Item, ' - ', ',') ItemModified
into #t from Test
Now when I run the following:
select * from #t
Now for example this is what i am getting.
ItemModified
Memphis,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 4
Memphis,405435,Pack 4,Not assigned
MEMPHIS,401273,Pack 12,0607043XX4X9
CHARLOTTE,400106,Number of items,0607040XQ2XX
Any quick help would be highly appreciated.
Thanks a million in advance.
|
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 12/05/2006 : 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 -SMYSERVER
hope 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
Sweden
29138 Posts |
Posted - 12/05/2006 : 16:59:06
|
Don't you even read the suggestion you get?
Peter Larsson
Helsingborg, Sweden |
|
|
|
zeeshan13
Constraint Violating Yak Guru
USA
347 Posts |
Posted - 12/05/2006 : 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
Sweden
29138 Posts |
Posted - 12/05/2006 : 17:04:43
|
Why do I even continue to bother?
Have you tried my suggestion I gave to you almost an hour ago?
Peter Larsson
Helsingborg, Sweden |
|
|
|
zeeshan13
Constraint Violating Yak Guru
USA
347 Posts |
Posted - 12/05/2006 : 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
Sweden
29138 Posts |
Posted - 12/05/2006 : 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 work
select 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 Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/05/2006 : 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 12/05/2006 17:19:24 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 12/05/2006 : 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
Sweden
29138 Posts |
Posted - 12/05/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
zeeshan13
Constraint Violating Yak Guru
USA
347 Posts |
Posted - 12/05/2006 : 17:38:48
|
No you dont have to bet :D
It 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
Sweden
29138 Posts |
Posted - 12/05/2006 : 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 12/05/2006 17:46:38 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/05/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 12/05/2006 : 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
USA
347 Posts |
Posted - 12/05/2006 : 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
Sweden
29138 Posts |
Posted - 12/06/2006 : 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 Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 12/06/2006 01:40:41 |
|
|
| |
Topic |
|
Breaking a nvarchar filed in to other fileds
