October 2, 2008 at 10:54 am
Ok, I'm sure someone has a better way of separating this data than using CHARINDEX....
10.1.1.%
10.%
10.1.%
99.%
99.1.%
99.1.1.%
(a whole lot more iterations similar to above)
My goal would be to get them into seperate temp tables based on the number of "." separators that they have. So, tables would be as follows;
T1
10.
99.
T2
10.1
99.1
T3
10.1.1
99.1.1.
I'm hoping there is some cooler way of doing this than a really long contrived charindex string statement.
Thoughts?
Edited to make the question clearer. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 2, 2008 at 11:10 am
I'm not clear on the goal. Could you write it out for your sample data so we have a clear example?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
October 2, 2008 at 11:11 am
Thanks Ryan. I just edited the original post as I realized it wasn't clear to me. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 2, 2008 at 11:17 am
Something along these lines?
declare @t table (v varchar(50))
insert @t
select '10.1.1.%'
union all select '10.%'
union all select '10.1.%'
union all select '99.%'
union all select '99.1.%'
union all select '99.1.1.%'
select *, len(v) - len(replace(v, '.', '')) as length from @t
select * into #t1 from @t where len(v) - len(replace(v, '.', '')) = 1
select * into #t2 from @t where len(v) - len(replace(v, '.', '')) = 2
select * into #t3 from @t where len(v) - len(replace(v, '.', '')) = 3
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
October 2, 2008 at 11:28 am
Cool Ryan! Thank you!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply