SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Breaking a nvarchar filed in to other fileds
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 12/05/2006 :  16:04:57  Show Profile  Reply with Quote
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
30282 Posts

Posted - 12/05/2006 :  16:12:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/05/2006 :  16:16:48  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 12/05/2006 :  16:47:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/05/2006 :  16:59:05  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/05/2006 :  16:59:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Don't you even read the suggestion you get?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 12/05/2006 :  17:01:24  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/05/2006 :  17:04:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
347 Posts

Posted - 12/05/2006 :  17:11:07  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/05/2006 :  17:17:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/05/2006 :  17:18:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/05/2006 :  17:26:43  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/05/2006 :  17:31:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
347 Posts

Posted - 12/05/2006 :  17:38:48  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/05/2006 :  17:42:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/05/2006 :  17:43:51  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/05/2006 :  17:44:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 12/05/2006 :  17:50:32  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

USA
347 Posts

Posted - 12/05/2006 :  17:51:50  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 12/06/2006 :  01:34:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000