October 15, 2013 at 1:05 pm
Hello Everyone
I need one help to develop logic in my SQL Script
In one file we have values as below
Periods
X
X
X X
X X
X X
I need to display their actual position where X is see below
PeriodsPeriods
X 3
X 3
X X 3,8
X X 5,9
X X 3,10
I try with LEN Function and I get Position where there is only one X but There are some case where we have 2 X so We need to display first Number then Comma and then Second Number.
Please help me do this
Thanks
October 15, 2013 at 1:21 pm
Seems a bit like homework.
I didn't test the query because you didn't provide table DDL and sample data, but this might be a step in the right direction:
SELECT
Result = CONVERT(VARCHAR(5),TempResult) + CASE WHEN CHARINDEX(periods,'X',TempResult + 1) = 0
THEN ''
ELSE ',' + CONVERT(VARCHAR(5),CHARINDEX(periods,'X',TempResult + 1))
END
FROM
(
SELECT
periods
,TempResult = CHARINDEX(periods,'X',0)
FROM myTable
) tmp;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 15, 2013 at 1:26 pm
Hi
This should do what you want. There is likely to be other better ways though
with sampledata as (
SELECT *
FROM (VALUES
(1,' X')
,(2,' X')
,(3,' X X')
,(4,' X X')
,(5,' X X')
) AS SD(ID, Periods)
),
cteTally as (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t1(N) --10
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t2(N) --100
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t3(N) --1000
),
indexPos as
(
SELECT ID, N
FROM sampledata s
CROSS APPLY (SELECT TOP(LEN(Periods)) N FROM cteTally) t
WHERE SUBSTRING(Periods,N,1) = 'X'
)
SELECT ID, Periods, SUBSTRING(Positions,2,100) Positions
FROM sampleData s
CROSS APPLY (
SELECT ',' + CAST(N AS VARCHAR(10)) AS [text()]
FROM indexPos i
WHERE s.ID = i.ID
FOR XML PATH('')
) c(Positions)
October 15, 2013 at 1:30 pm
Here is DDL and sample Data
CREATE TABLE #TEMP
(vcPeriods char(20))
INSERT INTO #TEMP VALUES (' X ')
INSERT INTO #TEMP VALUES (' X ')
INSERT INTO #TEMP VALUES (' X ')
INSERT INTO #TEMP VALUES (' XX ')
INSERT INTO #TEMP VALUES (' XXX X ')
INSERT INTO #TEMP VALUES (' XXXX ')
I Try Your Query but did not work for my need
Can You please try this and send me Query.
That will be great help for me
Thanks for Help
October 15, 2013 at 1:35 pm
yogi123 (10/15/2013)
I Try Your Query but did not work for my needCan You please try this and send me Query.
That will be great help for me
Thanks for Help
That's because I accidentally switched the expressions in the charindex function.
Correct code:
CREATE TABLE #TEMP
(vcPeriods char(20))
INSERT INTO #TEMP VALUES (' X ')
INSERT INTO #TEMP VALUES (' X ')
INSERT INTO #TEMP VALUES (' X ')
INSERT INTO #TEMP VALUES (' XX ')
INSERT INTO #TEMP VALUES (' XXX X ')
INSERT INTO #TEMP VALUES (' XXXX ')
SELECT
Result = CONVERT(VARCHAR(5),TempResult) + CASE WHEN CHARINDEX('X',vcPeriods,TempResult + 1) = 0
THEN ''
ELSE ',' + CONVERT(VARCHAR(5),CHARINDEX('X',vcPeriods,TempResult + 1))
END
FROM
(
SELECT
vcPeriods
,TempResult = CHARINDEX('X',vcPeriods,0)
FROM #TEMP
) tmp;
(why on earth did they decide on this sequence for the parameters in charindex. In the function replace they are the other way around)
You should find such stupid little bugs in code yourself.
The query I wrote searches for the first two occurences of X, because that's what your sample data and desired output in your original question dictated.
Your new sample data however has multiple X characters, so you better take a look at the code of mickyT.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 15, 2013 at 1:38 pm
Awesome Thanks for Your Help !!!
October 15, 2013 at 1:47 pm
Here is another way to do this. I added an identity column as a primary key.
This should work for any combination of spaces and X's.
See the link in my signature about splitting string for the DelimitedSplit8K function.
create table #Periods
(
id int identity primary key,
SomeValue varchar(10)
)
insert #Periods
select ' X ' union all
select ' X ' union all
select ' X X ' union all
select ' X X ' union all
select ' X X ' union all
select ' X X X X X';
with mySplit as
(
select *, ROW_NUMBER() over(PARTITION by ID order by ID desc) - 1 as RowModifier --this is used to control how many steps to move in the next query
from #Periods
cross apply dbo.DelimitedSplit8K(SomeValue, ' ')
where Item > ''
)
select ID,
STUFF((select ',' + cast(ItemNumber + RowModifier as varchar(3))
from mySplit s2
where s1.id = s2.id
order by s2.ItemNumber
FOR XML PATH('')), 1, 1, '')
from mySplit s1
group by id
drop table #Periods
_______________________________________________________________
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/
October 15, 2013 at 1:54 pm
One of these days I'll have to learn how to use CROSS APPLY. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 15, 2013 at 1:55 pm
Sean Lange (10/15/2013)
Here is another way to do this. I added an identity column as a primary key.This should work for any combination of spaces and X's.
See the link in my signature about splitting string for the DelimitedSplit8K function.
create table #Periods
(
id int identity primary key,
SomeValue varchar(10)
)
insert #Periods
select ' X ' union all
select ' X ' union all
select ' X X ' union all
select ' X X ' union all
select ' X X ' union all
select ' X X X X X';
with mySplit as
(
select *, ROW_NUMBER() over(PARTITION by ID order by ID desc) - 1 as RowModifier --this is used to control how many steps to move in the next query
from #Periods
cross apply dbo.DelimitedSplit8K(SomeValue, ' ')
where Item > ''
)
select ID,
STUFF((select ',' + cast(ItemNumber + RowModifier as varchar(3))
from mySplit s2
where s1.id = s2.id
order by s2.ItemNumber
FOR XML PATH('')), 1, 1, '')
from mySplit s1
group by id
drop table #Periods
Thanks Sean for Your Help
October 15, 2013 at 2:04 pm
Koen Verbeeck (10/15/2013)
One of these days I'll have to learn how to use CROSS APPLY. 😀
Paul's 2 part series explains it perfectly.
http://www.sqlservercentral.com/articles/APPLY/69953/[/url]
http://www.sqlservercentral.com/articles/APPLY/69954/[/url]
_______________________________________________________________
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/
October 15, 2013 at 2:29 pm
Since you inputs are char(20) the fastest (but least flexible) option would be something like
select id, somevalue, positions =
stuff(
case substring(SomeValue,1,1) when 'X' then ',1' else '' end +
case substring(SomeValue,2,1) when 'X' then ',2' else '' end +
case substring(SomeValue,3,1) when 'X' then ',3' else '' end +
case substring(SomeValue,4,1) when 'X' then ',4' else '' end +
case substring(SomeValue,5,1) when 'X' then ',5' else '' end +
case substring(SomeValue,6,1) when 'X' then ',6' else '' end +
case substring(SomeValue,7,1) when 'X' then ',7' else '' end +
case substring(SomeValue,8,1) when 'X' then ',8' else '' end +
case substring(SomeValue,9,1) when 'X' then ',9' else '' end +
case substring(SomeValue,10,1) when 'X' then ',10' else '' end +
case substring(SomeValue,11,1) when 'X' then ',11' else '' end +
case substring(SomeValue,12,1) when 'X' then ',12' else '' end +
case substring(SomeValue,13,1) when 'X' then ',13' else '' end +
case substring(SomeValue,14,1) when 'X' then ',14' else '' end +
case substring(SomeValue,15,1) when 'X' then ',15' else '' end +
case substring(SomeValue,16,1) when 'X' then ',16' else '' end +
case substring(SomeValue,17,1) when 'X' then ',17' else '' end +
case substring(SomeValue,18,1) when 'X' then ',18' else '' end +
case substring(SomeValue,19,1) when 'X' then ',19' else '' end +
case substring(SomeValue,20,1) when 'X' then ',20' else '' end
,1,1,'')
from #Periods
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply