May 16, 2013 at 2:48 pm
Hi,
Not sure how to start this, or even explain it right....
I have this "Node path" field on a table looks like
(@@)@@/1/140/@@/1/2/3/84/6/11/12/82/7/28/@@/1/2/3/84/6/11/12/82/7/28/76/@@/1/2/3/84/6/11/12/82/7/28/76/77/@@
after looking it up each set between @@ is a path with the last number set = node_id on another table
for above 140 = transaction
28 = client
76 = transport
etc......
any ideas on how to parse and look up the values?
Thanks
May 16, 2013 at 3:04 pm
jbalbo (5/16/2013)
Hi,Not sure how to start this, or even explain it right....
I have this "Node path" field on a table looks like
(@@)@@/1/140/@@/1/2/3/84/6/11/12/82/7/28/@@/1/2/3/84/6/11/12/82/7/28/76/@@/1/2/3/84/6/11/12/82/7/28/76/77/@@
after looking it up each set between @@ is a path with the last number set = node_id on another table
for above 140 = transaction
28 = client
76 = transport
etc......
any ideas on how to parse and look up the values?
Thanks
Going to need a LOT more information than that. Are you just trying to parse this string into something usable? Not sure what you mean about looking up the values in another table? Are there tables for transaction, client and transport?
Going to need ddl and sample data before we can get much further I think.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2013 at 3:13 pm
thanks for getting back...
so on table assessment there is a field called assessment_nodes and has the values like
(@@)@@/1/140/@@/1/2/3/84/6/11/12/82/7/28/@@/1/2/3/84/6/11/12/82/7/28/76/@@/1/2/3/84/6/11/12/82/7/28/76/77/@@
representing a path of a tree where assesements are
each last set on numbers represent Node_id on node table so an assessment record can have a number of node_ids
so @@/1/140/@@ part of assessment_node represents 140 for Node_Id, and name for 140 = transport
@@/1/2/3/84/6/11/12/82/7/28/@@ part of assessment_node represents 28 for Node_Id, and name for 28 = transport
etc.. between the @@
Make any more sense?
Thanks
May 16, 2013 at 3:18 pm
jbalbo (5/16/2013)
thanks for getting back...so on table assessment there is a field called assessment_nodes and has the values like
(@@)@@/1/140/@@/1/2/3/84/6/11/12/82/7/28/@@/1/2/3/84/6/11/12/82/7/28/76/@@/1/2/3/84/6/11/12/82/7/28/76/77/@@
representing a path of a tree where assesements are
each last set on numbers represent Node_id on node table so an assessment record can have a number of node_ids
so @@/1/140/@@ part of assessment_node represents 140 for Node_Id, and name for 140 = transport
@@/1/2/3/84/6/11/12/82/7/28/@@ part of assessment_node represents 28 for Node_Id, and name for 28 = transport
etc.. between the @@
Make any more sense?
Thanks
Not much more clear really. The best way to present your issue is with ddl and sample data. Remember we can't see your screen, we don't know your data, your system or what you are trying to do.
Something like this would be one way to parse it. Of course I have no idea what you are trying to do with it so I have no idea if this is helpful at all.
declare @SomeString varchar(200) = '(@@)@@/1/140/@@/1/2/3/84/6/11/12/82/7/28/@@/1/2/3/84/6/11/12/82/7/28/76/@@/1/2/3/84/6/11/12/82/7/28/76/77/@@';
with RawValues as
(
select *
from dbo.DelimitedSplit8K(replace(@SomeString, '@@', '@'), '@')
where Item > ''
and Item > ')'
)
, MyReversed as
(
select Stuff(REVERSE(Item), 1, 1, '') as Reversed
, ROW_NUMBER() over (Order by Item) as RowNum
,*
from RawValues
)
select Reverse(LEFT(Reversed, charindex('/', Reversed, 0) - 1)) as MyValue
, case RowNum when 1 then 'Transaction' when 2 then 'Client' when 3 then 'Transport' when 4 then 'etc' else 'Unknown' end as LookupType
--,*
from MyReversed
You can find the code the DelimitedSplit8K by following the link in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply