Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Breaking a nvarchar filed in to other fileds

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 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

30421 Posts

Posted - 2006-12-05 : 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
Go to Top of Page

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 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
Go to Top of Page

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, ' - ', ',') 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.



Go to Top of Page

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 -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
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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....
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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...
Go to Top of Page

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 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
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-12-05 : 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....


Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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
Go to Top of Page

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....
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -