the Parse replace function provided used in one table does not work for another table

Here is the function that worked for the first table:
select [Billed Code],
       ltrim(rtrim(PARSENAME(REPLACE([Billed Code],':','.'),3))) FirstItem,
       ltrim(rtrim(PARSENAME(REPLACE([Billed Code],':','.'),2))) SecondItem,
       ltrim(rtrim(PARSENAME(REPLACE([Billed Code],':','.'),1))) ThirdItem
  from dbo.tableresults
example of column data:  J6670 :  : 1
85900 :  : 2
10550 : LT : 1
20900 :  : 1
29879 : 22 : 1

I have the same data in a different table and using the parse(replace) but the 1st, 2nd, 3rd items are showing null and I checked the column and the data exists.  The datatype is text.  Any suggestions why it works for the first table and not the second table?
thanks
bar0822Asked:
Who is Participating?
 
cyberkiwiCommented:
A variation of

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26202788.html#a32814459

Will break up to 10 items.  You can remove some lines, such as I6-I10 if you only need 5 items
declare @t table (rowid int identity, definition varchar(max))
insert @t select '19328 : 50 :  59 :   : 1'
insert @t select '22612 : 82 :    :    : 1'
insert @t select '63046 :  :   :   : 1'
insert @t select '22852 : 51 :   AS :    : 1'
insert @t select '75685: 26 :   LT :    : 1'
insert @t select ':  :   :   : '

----------- FROM HERE IS WHERE YOUR CODE STARTS
;with
tbl(d, rn, rowid) as
(
	select definition, rn=ROW_NUMBER() over (order by rowid), rowid from @t
),
cte(v, cur) as
(
	select convert(varchar(max),''), 0
	union all
	select convert(varchar(max),cte.v + '<c id="'+LTRIM(str(t.rowid))+'" def='
		+QuoteName(t.d,'"')+'><b>' +  Replace(t.d, ':', '</b><b>') + '</b></c>'), convert(int,t.rn)
	from cte inner join tbl t on t.rn = cte.cur+1
)
select
	c.value('@id', 'int') RowID,
	c.value('@def', 'varchar(max)') definition,
	c.value('b[1]', 'varchar(max)') FirstItem,
	c.value('b[2]', 'varchar(max)') SecondItem,
	c.value('b[3]', 'varchar(max)') ThirdItem,
	c.value('b[4]', 'varchar(max)') FourthItem,
	c.value('b[5]', 'varchar(max)') FifthItem,
	c.value('b[6]', 'varchar(max)') I6,
	c.value('b[7]', 'varchar(max)') I7,
	c.value('b[8]', 'varchar(max)') I8,
	c.value('b[9]', 'varchar(max)') I9,
	c.value('b[10]', 'varchar(max)') I10
from (select top 1 convert(xml,'<a>'+v+'</a>') as xm from cte order by cur desc) sq
cross apply sq.xm.nodes('/a/c') as a(c)

Open in new window

0
 
bar0822Author Commented:
let me add to that:  
the table that the function does not work on was imported into access database with text datatype but the original table was on sql with varchar type.  

Tested the function on the actual table in sql and it worked fine but when I used it on the table that was imported into Access as text then imported into SQL as varchar - does not work.  There seems to be a problem with the table that was imported from Access as text.
0
 
knightEknightCommented:
It depends on the data.  Run the script below to see some of the possibilities.  Note that the last statement returns nulls all the way thru.


declare @name varchar(80)

select  @name = 'A'
select PARSENAME(@name,0), PARSENAME(@name,1), PARSENAME(@name,2), PARSENAME(@name,3), PARSENAME(@name,4), PARSENAME(@name,5)

select  @name = 'A.B'
select PARSENAME(@name,0), PARSENAME(@name,1), PARSENAME(@name,2), PARSENAME(@name,3), PARSENAME(@name,4), PARSENAME(@name,5)

select  @name = 'A.B.C'
select PARSENAME(@name,0), PARSENAME(@name,1), PARSENAME(@name,2), PARSENAME(@name,3), PARSENAME(@name,4), PARSENAME(@name,5)

select  @name = 'A.B.C.D'
select PARSENAME(@name,0), PARSENAME(@name,1), PARSENAME(@name,2), PARSENAME(@name,3), PARSENAME(@name,4), PARSENAME(@name,5)

select  @name = 'A.B.C.D.E'
select PARSENAME(@name,0), PARSENAME(@name,1), PARSENAME(@name,2), PARSENAME(@name,3), PARSENAME(@name,4), PARSENAME(@name,5)

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
SharathData EngineerCommented:
What output are you getting on the second table with your query?
0
 
bar0822Author Commented:
the out put in the second query are all nulls.

but here is a sample from the second table:
19328 : 50 :  59 :   : 1
22612 : 82 :    :    : 1
63046 :  :   :   : 1
22852 : 51 :   AS :    : 1
75685: 26 :   LT :    : 1
:  :   :   :
thanks!
0
 
knightEknightCommented:
I think that function takes at most 3 delimiters, as noted above:

select  @name = 'A.B.C.D.E'
select PARSENAME(@name,0), PARSENAME(@name,1), PARSENAME(@name,2), PARSENAME(@name,3), PARSENAME(@name,4), PARSENAME(@name,5)
0
 
knightEknightCommented:
note the 4 delimiters (dots) in the query above causes all NULLs to be returned.
0
 
bar0822Author Commented:
I am just starting out in sql programming and I find this confusing - can you go into more detail?  I am not sure what you mean with the 4 dots and how is it causing nulls - is there another solution?

thanks all for your speedy replies, appreciate.
0
 
knightEknightCommented:
The PARSENAME function is not intended as a generic string parsing function (although I wish it was).  It can parse IP addresses and SQL Server domain object names well, but other than these (and strings in similar format) it is limited.

See this explaination:  http://weblogs.sqlteam.com/jeffs/archive/2003/09/30/195.aspx

However, I have pre-written functions to parse strings to columns, but as a group they are a bit lengthy.  Let me know if this will help you and I will post them.
0
 
bar0822Author Commented:
I am realizing that the first parse string works only if there are three columns but anything more creates null values.

If this the only way to get the data out then would appreciate if you could provide a function that I could use - hopefully not too complicated.  I need the data to create reports and cannot do so completed until the column is parsed and the clock is ticking.
Thanks,
0
 
knightEknightCommented:
How many columns do you need?
0
 
Anthony PerkinsCommented:
Award points here http:#a35436490
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.