parsing a field into 3 seperate fields

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

General SQL Server Forums

New to SQL Server Programming

parsing a field into 3 seperate fields

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-14 : 00:00:23

I currently have a table which contains a field (Customer) which holds 3 different elements of data seperated by a | . For example:

John Smith |5555 N. Holland|New York
Mary C. Johnson|9292 S. Cleveland Rd.|California

The | is never in the same position. Is there a way to parse this data out and store it to a new table as 3 seperate fields along with the original field (i.e. customer, name, address, city).

Can someone help me with the code? I am not exactly certain how to go about this. Thanks again for your time.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-14 : 00:24:24

One way to get the required output:

CREATE FUNCTION dbo.GetSplitAdd (@array VARCHAR(1000),@separator VARCHAR(10))
RETURNS @T Table (col1 varchar(100),col2 varchar(100),col3 varchar(100))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(100)
declare @occurence int
Declare @Addr1 varchar(100)
Declare @Addr2 varchar(100)
Declare @Addr3 varchar(100)

set @occurence =1 -- to get the Nth occurence
if (left(@array,len(@separator))=@separator)
begin
set @array=stuff(@array,1,len(@separator),'')
end
if (right(@array,len(@separator))<>@separator)
begin
SET @array = @array + @separator
end

WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
-- INSERT into @T VALUES (@occurence,@array_value)

if @occurence = 1
set @Addr1 = @array_value
else if @occurence = 2
set @Addr2 = @array_value
else if @occurence = 3
set @Addr3 = @array_value

set @occurence = @occurence + 1
SELECT @array = STUFF(@array,1, @separator_position + len(@separator)-1, '')
END

Insert into @T Values (@Addr1,@Addr2,@Addr3)

RETURN
END
--
-- Example to call
select * from dbo.GetSplitAdd ('Mary C. Johnson|9292 S. Cleveland Rd.|California','|')

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-07-14 : 05:03:29

[code]
try this one
DECLARE @str TABLE(data VARCHAR(128))
INSERT INTO @str SELECT 'John Smith |5555 N. Holland|New York'
UNION ALL SELECT 'Mary C. Johnson|9292 S. Cleveland Rd.|California'

SELECT s.rid,
ROW_NUMBER()OVER(PARTITION BY rid ORDER BY rid) as id,
REPLACE(SUBSTRING(s.data,charindex('|',s.data,v.number),abs(charindex('|',s.data,charindex('|',s.data,v.number)+1)-charindex('|',s.data,v.number))),'|','')as value
INTO #t
FROM (select row_number()over(order by data) as rid, * from @str) AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
AND v.number > 0
AND v.number <= len(s.data)
WHERE substring('|' + s.data, v.number, 1) = '|'

SELECT [3] AS customer,[1] AS Name,[2]Address FROM #t
PIVOT (MAX(value) FOR id IN ([1],[2],[3]))s

DROP TABLE #t

--2nd method
SELECT REPLACE(REPLACE(data,'.','^'),'|','.') AS val INTO #tt FROM @str

SELECT REPLACE(PARSENAME(val,3),'^','.') AS Customer,
REPLACE(PARSENAME(val,2),'^','.') AS Name,
REPLACE(PARSENAME(val,1),'^','.') AS Address FROM #tt

DROP TABLE #tt
[/code]

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-14 : 07:29:10

My sincere apologies. I have worded what I needed incorrectly. I meant to say I need to parse the data so that I can display the 3 fields separately in a select statement so that I can then display them in a report. Hoping you can still help. Thanks once again.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-14 : 09:09:39

quote:
Originally posted by cirugio

My sincere apologies. I have worded what I needed incorrectly. I meant to say I need to parse the data so that I can display the 3 fields separately in a select statement so that I can then display them in a report. Hoping you can still help. Thanks once again.

The below example use the function dbo.GetSplitAdd. (code of the same can be found in my earlier post for the thread).

Try this:

-- Example to call
select Col1 as Name, Col2 as Address, Col3 as City from dbo.GetSplitAdd ('Mary C. Johnson|9292 S. Cleveland Rd.|California','|')

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-14 : 18:07:32

Hi Bohra,

It looks like from your example, that the information is saved to a file called GetSplitAdd. Right? My apologies but I really messed up on how I was trying to communicate what I was really trying to accomplish. I will try to be clearer this time.

What I really need to do is write a select statement which will display the data of the CUSTOMER field as 3 seperate fields in a report. For example my table dbo.client currently contains a field called CUSTOMER. The value in this field is made up of 3 different elements (name, address, city) seperated by "|". The | is not in a constant position. For example this is what the CUSTOMER field may look like:

John Smith |5555 N. Holland|New York
Mary C. Johnson|9292 S. Cleveland Rd.|California

I just need to write a report that would display the information like this. Just not sure how to do it??
I am assuming some substring, just don't know how to write it.



Name                Address                   City 
------------      ---------------         ---------------
John Smith        5555 N. Holland           New York 
Mary C. Johnson   9292 S. Cleveland Rd.     California

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 12:32:04

Hi Cirugio,

quote:

It looks like from your example, that the information is saved to a file called GetSplitAdd. Right? My apologies but I really messed up on how I was trying to communicate what I was really trying to accomplish. I will try to be clearer this time.

GetSplitAdd is the user defined function and will split the passed input value into three different values based on the parameter and separator.

Try solution given by bklr. I feel it should meet your requirement.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-15 : 12:54:42

Thanks. I will give it a try. I am so new to this, that it is a bit confusing.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-07-15 : 13:28:07

Hi cirugio

Why don't you just use substring.

Run the code below to see if it works for you.

------------------

declare @temp table(OC varchar(200))
insert into @temp values('John Smith |5555 N. Holland|New York')
insert into @temp values('Mary C. Johnson|9292 S. Cleveland Rd.|California')

select
substring(OC, 0 ,charIndex('|', OC)) AS NAME,
substring(OC, charIndex('|', OC)+1, (charIndex('|', OC, charIndex('|', OC)+1) - charIndex('|', OC)) -1) AS ADDRESS,
substring(OC, charIndex('|', OC, charIndex('|', OC)+1)+1, len(OC)) AS CITY
from @temp

------------------

My Test Result:

NAME ADDRESS CITY
John Smith 5555 N. Holland New York
Mary C. Johnson 9292 S. Cleveland Rd. California

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-15 : 17:01:45

Hi Namman,

This is perfect. I totally get this. I tried implementing this (see code below)but I am get the following error "Invalid length parameter passed to the substring function." Thoughts??

 
select 
substring(AccountName, 0 ,charIndex(':', AccountName)) AS NAME,
substring(AccountName, charIndex(':', AccountName)+1, (charIndex(':', AccountName, charIndex(':', AccountName)+1) - charIndex(':', AccountName)) -1) AS ADDRESS,
substring(AccountName, charIndex(':', AccountName, charIndex(':', AccountName)+1)+1, len(AccountName)) AS CITY
from accounts

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 23:24:14

quote:
Originally posted by cirugio

Hi Namman,

This is perfect. I totally get this. I tried implementing this (see code below)but I am get the following error "Invalid length parameter passed to the substring function." Thoughts??

select
substring(AccountName, 0 ,charIndex(':', AccountName)) AS NAME,
substring(AccountName, charIndex(':', AccountName)+1, (charIndex(':', AccountName, charIndex(':', AccountName)+1) - charIndex(':', AccountName)) -1) AS ADDRESS,
substring(AccountName, charIndex(':', AccountName, charIndex(':', AccountName)+1)+1, len(AccountName)) AS CITY
from accounts

Hi Cirugio,

As per your original post, you are using pipe "|" symbol as separator. then why are you using ':' in your select statement.
If you are using '|' as separator then replace all ':' with '|' in your select statement.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 00:04:17

It really should be the : symbol. I had a memory lapse when I typed my post, but yes it should be :

Upon running Namman's example it works great. But when I applied my logic and read from a table which is in production, I receive that error. Does it matter if the ACCOUNT field is defined as a Varchar on the table its reading from?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-16 : 00:19:22

quote:
Originally posted by cirugio

It really should be the : symbol. I had a memory lapse when I typed my post, but yes it should be :

Upon running Namman's example it works great. But when I applied my logic and read from a table which is in production, I receive that error. Does it matter if the ACCOUNT field is defined as a Varchar on the table its reading from?

You get the error when one of the row is having only 1 separator and not two.

An small modified example for raising the error:

declare @temp table(OC varchar(200))
insert into @temp values('John Smith |5555 N. Holland|New York')
insert into @temp values('Mary C. Johnson|9292 S. Cleveland Rd.California')

select
substring(OC, 0 ,charIndex('|', OC)) AS NAME,
substring(OC, charIndex('|', OC)+1, (charIndex('|', OC, charIndex('|', OC)+1) - charIndex('|', OC)) -1) AS ADDRESS,
substring(OC, charIndex('|', OC, charIndex('|', OC)+1)+1, len(OC)) AS CITY
from @temp

In the second insert statement, I kept only 1 pipe separator.

I am here to learn from Masters and help new bees in learning.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 01:01:22

Hi Bohra,

I scanned through the entire table and apparently I found that there is really 3 delimiters. The 3rd delimiter is always at the end. For example, the data would actually look something like this:

John Smith |5555 N. Holland|New York|
Mary C. Johnson|9292 S. Cleveland Rd.|California|

My apologies for all my errors, its just been a very long day. Hoping you can help me out with this code once more. Thanks again.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-16 : 02:20:25

Can you execute the below query and post the output:

select distinct len(AccountName) - len(replace(AccountName,':','')) from accounts
order by 1

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 02:51:33

The results are:

0
3
4

If I could just get the first two set information (name and address) I would be ok. I really don't need the city , if it makes it any easier.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-16 : 02:55:15

select
replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),2),'#~@','.'),
replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),3),'#~@','.'),
replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),4),'#~@','.')
from accounts

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-16 : 03:08:45

Small modification in webfred code:

select
replace(parsename(replace(replace(AccountName,'.','#~@'),':','.'),3),'#~@','.') as Name,
replace(parsename(replace(replace(AccountName,'.','#~@'),':','.'),2),'#~@','.') as Address,
replace(parsename(replace(replace(AccountName,'.','#~@'),':','.'),1),'#~@','.') as City
from Accounts

Example:

declare @temp table(AccountName varchar(200))
insert into @temp values('John Smith :5555 N. Holland:New York')
insert into @temp values('Mary C. Johnson:9292 S. Cleveland Rd.:California')

select
replace(parsename(replace(replace(AccountName,'.','#~@'),':','.'),3),'#~@','.') as Name,
replace(parsename(replace(replace(AccountName,'.','#~@'),':','.'),2),'#~@','.') as Address,
replace(parsename(replace(replace(AccountName,'.','#~@'),':','.'),1),'#~@','.') as City
from @temp

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 04:15:15

I implemented the logic on my side and I am getting nulls when I apply the replace(parsename....) logic into my program. I noticed your sample did not have a : at the end of the last string. I think is why I get Nulls. The data actually looks like:

John Smith :5555 N. Holland:New York:
Mary C. Johnson:9292 S. Cleveland Rd.:California:

I am not sure exactly how to change the code for I do not understand how it all works. I looked up the parsename command, to try to figure out exactly what the logic you provided me does but couldn't find any reference to the #~@. Hoping you can still assist with the code and maybe provide me with a quick explanation on how it all works.

Regards,
cirugio

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-16 : 04:34:36

quote:
Originally posted by cirugio

I implemented the logic on my side and I am getting nulls when I apply the replace(parsename....) logic into my program. I noticed your sample did not have a : at the end of the last string. I think is why I get Nulls. The data actually looks like:

John Smith :5555 N. Holland:New York:
Mary C. Johnson:9292 S. Cleveland Rd.:California:

I am not sure exactly how to change the code for I do not understand how it all works. I looked up the parsename command, to try to figure out exactly what the logic you provided me does but couldn't find any reference to the #~@. Hoping you can still assist with the code and maybe provide me with a quick explanation on how it all works.

Regards,
cirugio

Take my solution posted above - it should work.

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-16 : 04:42:55

Hi cirugio,

--Alternate solution

--First you need to create an udf in the db you are using, for getting the values at various position. Copy the function code and paste in query analyzer / SSMS and execute it.

Create FUNCTION GetStringAtPos (@array VARCHAR(1000),@separator VARCHAR(10),@Pos int)
RETURNS Varchar(1000)
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(100)
declare @occurence int
Declare @RetValue varchar(1000)

set @occurence =1 -- to get the Nth occurence
if (left(@array,len(@separator))=@separator)
begin
set @array=stuff(@array,1,len(@separator),'')
end

if (right(@array,len(@separator))<>@separator)
begin
SET @array = @array + @separator
end

WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)

if @Occurence = @Pos
Begin
set @RetValue = @array_value
RETURN @RetValue
End

set @occurence = @occurence + 1
SELECT @array = STUFF(@array,1, @separator_position + len(@separator)-1, '')
END
RETURN @RetValue
END
--Function code ends here.

--Now how to use the function:

Select
dbo.GetStringAtPos(AccountName,':',1 ) as Name,
dbo.GetStringAtPos(AccountName,':',2 ) as Address,
dbo.GetStringAtPos(AccountName,':',3 ) as City
from accounts

Note: This is little bit expensive opearation but i suggest that if it satisfies your requirement, go ahead till you get better solution from other experts.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources