Character's position frm a string

Question

Character's position frm a string

gugan_ta

SSCommitted

Points: 1649
More actions
August 20, 2009 at 10:31 am

#134885

D/A,
Need to find out the position of a charcter's in a string
Eg. (Position of "1" in a given string)
String : '000100010001'
Result should be : 4,8,12
Pls guide/help how to get the result set
Thanks....Gugan

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Take a look at this article[/url] written by Jeff Moden. About halfway through the article there is an example doing exactly this.

If you get stuck or have questions, post them here.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

declare @input varchar(100)

set @input = '000100010001'

;with tally (N) as (select row_number() over (order by id) from master..syscolumns)

select N from tally

where substring(@input,N,1) = '1'

and N <= len(@input)

edited to add:

Whups, sorry Seth. Didn't realize you had already posted.

That's a good article by Jeff and well worth reading.

To get 4,8,12 as one character string, use this.

--

declare @input varchar(100)

set @input = '000100010001'

--

;with tally (N) as (select row_number() over (order by id) from master..syscolumns)

--

,positions (N,Nstr) as

(select N,cast(N as varchar(5)) from tally

where substring(@input,N,1) = '1'

and N <= len(@input)

)

--

select stuff(( SELECT ',' + Nstr

FROM positions

ORDER BY N

FOR XML PATH('')

) ,1,1,'')

--

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills