August 24, 2009 at 5:07 am
D/A,
Pls help..to find the position of the character.
Comma should be replaced with "*" and psotion of "1" should be for each delimiter.
INPUT
Column1
'0001,01010,000101'
OUTPUT -- Positon of "1"
Column1 Column2
'0001,01010,000101' 4*2,4,*4,6*
Thanks...Gugan
August 24, 2009 at 7:57 am
You use the CHARINDEX function to find the position of a character.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 24, 2009 at 10:31 am
Double tally parse with xml concatenation. That was fun. 😀
For my next trick, I will attempt a double gainer with a half twist....
DECLARE @input varchar(100)
DECLARE @output varchar(100)
DECLARE @workTbl table (rowID int identity(1,1) primary key,element varchar(50), N int)
SET @input = '0001,01010,000101'
select @input as [before]
SET @input = ','+@input+','
-- using tally instead of XML because it parses quicker (in my time trials anyway)
;with tally (N) as (select row_number() over (order by ID) from master..syscolumns)
,cte1 (element) as
(select substring(@input,N+1,charindex(',',@input,N+1)-(N+1)) as N
from tally
where substring(@input,N,1) = ','
and N < len(@input)
)
,cte2 as (select element,N
from cte1
cross join tally
where N <= len(element)
and substring(element,N,1) = '1')
insert into @workTbl
select * from cte2
;with tally (N) as (select row_number() over (order by ID) from master..syscolumns)
,cte2 as (select min(rowID) as rowID, element,stuff((SELECT ',' + cast(N as varchar(5))
FROM @worktbl c2
WHERE c2.element = c1.element -- must match GROUP BY below
FOR XML PATH('')
),1,1,'') as [Concatenated]
from @worktbl c1
GROUP BY element -- without GROUP BY multiple rows are returned
)
select @output = stuff(( SELECT '*' + concatenated
FROM cte2
ORDER BY rowID
FOR XML PATH('')
) ,1,1,'')
select @output as [after]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2009 at 10:48 am
Very interesting requirement and Bob seems to have solved it nicely. Just curious, what is this for?
Also, if you don't know about Tally or Numbers tables and how they are used to replace loops, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2009 at 11:00 am
Good question. Gugan, are you dealing with the data from some kind of statistical analysis software? The stats wizards in our analytics group always view data as elements of an array. What are you planning to do with the data next?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2009 at 11:11 am
Jeff:
I have a question for you. I originally tried to do the whole problem without the working table, but I kept getting an error that an invalid length was being passed to the substring. I never changed any of the queries with the SUBSTRING function, I just split the two major steps by loading the work table and then running the second step using it as input.
Is using a tally table twice for different purposes likely to cause confusion?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2009 at 11:19 am
Heh... I've found that, for people who have never used it before, using it just once is enough to cause confusion. 😀
Personally, I see no reason not to use it more than once if the problem requires it.
This is an interesting problem and although I've got a pretty full dance card this week, I'm going to try to play with it because it seems like a fun thing to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2009 at 3:34 am
Hi Bob / Jeff,
Thanks a ton... for a wonderful query technique.
August 25, 2009 at 7:46 am
Most of the credit goes to Jeff. He taught me everything I know about Tally tables.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 7:57 am
Bob Hovious (8/25/2009)
Most of the credit goes to Jeff. He taught me everything I know about Tally tables.
:blush: Thanks, Bob. I really appreciate the kudo. Heh... with the solution of this problem, I'd say that you've taken it up a level. Well done.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2009 at 2:57 pm
Indeed a challenging question, so I gave it a shot too. I'm also curious about the purpose of the output, because it seams at least as complicated to process in T-SQL as the input.
set statistics time on;
select stuff(replace('*' +
(
select
case when t.c = '1' then ',' + t.p else '*' end
from
(
select top 100 percent
substring(s.Sequence, n.N, 1) as c,
cast(n.N - max(coalesce(t.N, 0)) as varchar(10)) p
from
(
select '0001,01010,000101' as Sequence
) s
cross join
dbo.Tally n
left join
dbo.Tally t on t.N <= n.N and substring(s.Sequence, t.N, 1) = ','
where
n.N <= len(s.Sequence)
group by
n.N, substring(s.Sequence, n.N, 1)
order by
n.N
) t
where
t.c '0'
for xml path('')
)
, '*,', '*'), 1, 1, '')
Some notes:
. The code runs slightly faster then Bob's solution. I guess because there's no need for a working table and the use of a persistent Tally table.
. The original question didn't say anything about what to do with comma-delimited subsequences without 1's. This solution produces '*1' for input '0,1' and '1*' for '1,0'. Bob's solution produces '1' in both cases. I leave it up to the OP which one is correct.
. I tried Bob's way to generate the Tally table, but it gave me incorrect results. I guess it had something to do with the order in derived table t, but sorting it gave still the same incorrect result. Hence the use of a persistent Tally table. Below the script to generate it.
create table dbo.Tally
(
N int identity primary key
)
declare @i int
select @i = 1
while @i <= 10000
begin
insert into Tally default values
select @i = @i + 1
end
Peter
August 25, 2009 at 3:43 pm
Nice work, Peter! I totally missed thinking about the issue of same values. Good catch.
I should go back and test my old code with a persisted tally table to see if it lets me avoid the error that forced me to use the temp table. I knew that loading it before going to the second step would definitely slow things down.
It would also be interesting to know if there should be warning labels on "virtual" tally tables.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 3:51 pm
Thanks Bob.
BTW, I figured out wat was wrong with using a CTE to generate the Tally table. It was the final SELECT ... FOR XML PATH('') which ruined the order.
Here's the alternative with the CTE, although it's still a little bit slower (and the execution plan is far more complex):
set statistics time on;
with Number(N) as
(
select row_number() over (order by ID) N from master..syscolumns
)
select stuff(replace('*' +
(
select
case when t.c = '1' then ',' + t.p else '*' end
from
(
select
n.N,
substring(s.Sequence, n.N, 1) as c,
cast(n.N - max(coalesce(t.N, 0)) as varchar(10)) p
from
(
select '0001,01010,000101' as Sequence
) s
cross join
Number n
left join
Number t on t.N <= n.N and substring(s.Sequence, t.N, 1) = ','
where
n.N <= len(s.Sequence)
group by
n.N, substring(s.Sequence, n.N, 1)
) t
where
t.c '0'
order by
t.N
for xml path('')
)
, '*,', '*'), 1, 1, '')
August 25, 2009 at 10:41 pm
Gugan and Peter,
I rewrote the CTE solution to account for strings that have no '1's. It now shows a zero to represent such strings. In the process, I reworked the CTE logic to improve performance. Oddly, the use of a pregenerated tally table showed no performance improvement in the revised CTE solution. Gugan, you may want to change over to this new code when you see the performance difference.
Would someone please doublecheck my results? For comparison, Peter's original code is shown in a slightly modified version below. It now takes the sequence string as an input variable, rather than a constant. Both versions are using dbo.Tally, and not the "virtual" tally cte. Accepting an input variable appears to slow things down quite a bit, and the presence of multiple strings of all zeroes appears (to me) to confuse Peter's results. But it's late, and I'm not at my sharpest. There may be room for improvement yet.
Gugan, thank you for posing this problem, and Peter, thank you for forcing me to rethink it. It has been a very worthwhile exercise.
-- Peters original solution, with modifications
set nocount on; -- added by bobH
declare @input varchar(1000) -- added by bobH
set @input = '0000,0001,1000,0000,01010,000101,0000'-- added by bobH
print '----- Peters Original Solution ------'-- added by bobH
set statistics time on;
select stuff(replace('*' +
(
select
case when t.c = '1' then ',' + t.p else '*' end
from
(
select top 100 percent
substring(s.Sequence, n.N, 1) as c,
cast(n.N - max(coalesce(t.N, 0)) as varchar(10)) p
from
(
select @input as Sequence -- modified by bobH
) s
cross join
dbo.Tally n
left join
dbo.Tally t on t.N <= n.N and substring(s.Sequence, t.N, 1) = ','
where
n.N <= len(s.Sequence)
group by
n.N, substring(s.Sequence, n.N, 1)
order by
n.N
) t
where
t.c '0'
for xml path('')
)
, '*,', '*'), 1, 1, '')
----- Peter's Original Solution ------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 726 ms.
-- revised CTE solution
set nocount on;
DECLARE @input varchar(100)
DECLARE @output varchar(100)
DECLARE @workTbl table (rowID int identity(1,1) primary key,element varchar(50), N int)
SET @input = '0000,0001,1000,0000,01010,000101,0000'
select @input as [before]
print '----- revised CTE solution -----'
set statistics time on;
SET @input = '*'+ replace(@input,',','*')
-- select @input as [after]
;with tally (N) as (select row_number() over (order by ID) from master..syscolumns)
,cte1 (position,value) as
(select N,substring(@input,N,1) from tally where substring(@input,N,1) in ('1','*') and N<=len(@input))
,cte2 (c1Pos,c1Val,c2Pos,c2Val,strValue) as
(select *,
case when c.Value = '*' and c2.Value = '*' then '0*'
when c.Value = '*' and c2.Value '*' then '*'
else null end as strValue
from cte1 c
join cte1 c2 on c2.position < c.position
where c.value = '*' or (c.value '*' and c2.value = '*')
)
,cte3 as (
select c1Pos,c1Val, min(strValue) as strValue, min(c1Pos-c2Pos) as bitPos
from cte2
group by c1Pos,c1Val
)
,cte4 (c1Pos,strValue) as (
select c1pos,case when strValue is not null then strValue else cast(bitPos as varchar(3))+',' end from cte3
)
,cte5 (result) as
(select ( SELECT top 100 percent '' + strValue
FROM cte4
ORDER BY c1Pos
FOR XML PATH('')
)
)
,cte6 (result) as
(select replace(replace(result,'**','*0*'),',*','*') from cte5)
select case when right(result,1) = '*' then result+'0'
when right(result,1) = ',' then stuff(result,len(result),1,'') else result end as result
from cte6
set statistics time off;
----- revised CTE solution -----
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 25, 2009 at 10:54 pm
I don't know why everything in the code above is showing up in red. Maybe the PRINT statement?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply