1

I have to write a select query to get the value from a comma delimited column based on the position. When i say position means every value between comma considered as one position.

Example:

Source table 'alphabets' has one column with value as below

Column1 abc,defg,hi,j,kl,mno,pqr,st,u,v,wx,yz

Now i have to select the 7th position value which is 'pqr' as my output in SQL Server 2012.

I have came up with temporary solution for the consistent length values but need help for selecting the inconsistent length values.

Can anyone help me on this scenario?

Thanks in Advance!!

sstan
  • 35,425
  • 6
  • 48
  • 66
Meet Rohan
  • 75
  • 2
  • 13

2 Answers2

1

you can split the string with comma:

;WITH alphabets(s)AS(
   SELECT 'abc,defg,hi,j,kl,mno,pqr,st,u,v,wx,yz'
)
SELECT l.* FROM alphabets AS a
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(a.s,',','</n><n>')+'</n>')))c(x)
CROSS APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS Pos,x.n.value('.','varchar(100)') AS ch FROM c.x.nodes('n')x(n)) l

Pos ch
1   abc
2   defg
3   hi
4   j
5   kl
6   mno
7   pqr
8   st
9   u
10  v
11  wx
12  yz
Nolan Shang
  • 2,312
  • 1
  • 14
  • 10
0

I tried the below sample.Which give the postion of the 7th row.Hope it resolves the problem.

create table #alphabets ( val varchar(100) ) insert into #alphabets values ('abc'), ('defg'), ('hi'), ('j'), ('kl'), ('mno'), ('c'), ('st'), ('u'), ('v'), ('wx'), ('yz')

SELECT ROW_NUMBER() OVER(ORDER BY val) as ronumber,val into #temp1 From #alphabets

select * from #temp1 where ronumber = 7

sivavamsi
  • 1
  • 2