Text Delimited information - SQL Server Forums

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)

Text Delimited information

Author

Topic

enrique
Starting Member

7 Posts

Posted - 2004-09-02 : 16:46:00

I have one field called Project Description like this:
"Nelson Gold/ESIA/Chore". First part Nelson Gold is my client, second is the job description and the third is the location. How can I extract to a view or a table in three separate fields called client, job_description and location.

The field delimitator will be /.

Thanks for your help

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-09-02 : 17:56:45

http://www.sqlteam.com/item.asp?ItemID=2652

enrique
Starting Member

7 Posts

Posted - 2004-09-03 : 09:54:49

I got it, thanks.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-03 : 14:07:36

Can u please show us the code u used

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-03 : 14:25:03

quote:
Originally posted by sqllearner

Can u please show us the code u used

Did you look at the link supplied?

Brett

8-)

enrique
Starting Member

7 Posts

Posted - 2004-09-03 : 14:41:59

I am using
SUBSTRING(PR01009, 1, CHARINDEX('/', PR01009) - 1) AS short_title_client
so from
"Nelson Gold/ESIA/Chore".
I am getting Nelson Gold that is what I want, the problem is that I am trying to get the job description (in this case ESIA)with no success.

Any sugestions?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 14:50:43

try:


Declare @str nvarchar(100)
Set @str = Replace('Nelson Gold/ESIA/Chore','/','.')

Select
	client = parsename(@Str , 1),
	job_description = parsename(@Str , 2),
	location = parsename(@Str , 3)

Corey

enrique
Starting Member

7 Posts

Posted - 2004-09-03 : 16:59:49

I am using this is in a View definition, so I cannot declare variables, so what I did was:
Select PARSENAME(REPLACE(dbo.PR010500.PR01009, '/', ','), 1) AS short_title_client, PARSENAME(REPLACE dbo.PR010500.PR01009, '/', ','), 2)AS short_title_description, PARSENAME(REPLACE dbo.PR010500.PR01009, '/', ','), 3)AS short_title_location

And I am not getting anything, Am I wrong?.

I would thank your help

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 17:02:25

yeah... you should be replacing the '/' with '.' not ','

so:


Select PARSENAME(REPLACE(dbo.PR010500.PR01009, '/', '.'), 1) AS short_title_client, PARSENAME(REPLACE dbo.PR010500.PR01009, '/', '.'), 2)AS short_title_description, PARSENAME(REPLACE dbo.PR010500.PR01009, '/', '.'), 3)AS short_title_location

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources