?
Solved

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

Posted on 2011-04-20
15
Medium Priority
?
264 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:bar0822
13 Comments
 

Author Comment

by:bar0822
ID: 35433425
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35433767
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35434244
What output are you getting on the second table with your query?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:bar0822
ID: 35434395
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35434416
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35434424
note the 4 delimiters (dots) in the query above causes all NULLs to be returned.
0
 

Author Comment

by:bar0822
ID: 35434561
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35434604
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
 

Author Comment

by:bar0822
ID: 35434896
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 35435360
How many columns do you need?
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1000 total points
ID: 35436490
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36000608
Award points here http:#a35436490
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 36120032
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question