Help Generating Patterns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help Generating Patterns

Author

Topic

sriv
Starting Member

11 Posts

Posted - 2009-04-06 : 14:22:06

Hi All,
I have a table which looks like this:

AutoNumber Note
1 C
2 D
3 E
4 F
5 G
6 A

Is there a stored procedure of function someone can suggest that would create patterns based on input of Autonumber.
For example, "123 234 456" should generate "CDE DEF FGA".
This is for a music application which helps generate practice sequences for piano students.

Thank you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-06 : 14:30:22

Why not just pass the actual notes, instead of numeric values that represent the notes?

sriv
Starting Member

11 Posts

Posted - 2009-04-06 : 15:00:59

Hi, Thanks for the reply. The sequence is generated based on pattern algorithms. For eg, Play consecutive notes, Play notes in sequence: first note then fourth then third then second, play each note 3 times etc etc.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-06 : 16:40:09

OK, that make sense. Will you get IDs that are 2 or more digits in length? If so that will complicate the sequence, i.e. 11 vs. 2 sequential 1's.

sriv
Starting Member

11 Posts

Posted - 2009-04-06 : 16:52:01

There can be a max of 8 notes in this particular case , so no, there wont be any double digits. Thanks.

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-07 : 04:16:08

create proc sc_convertAutoNumbers
(
@str varchar(100)
)
as
begin
;with cte as
(
select 1 as Number
union all
select Number + 1 from cte
where Number < len(@str)
)
select isnull(Note,substring(@str,C.Number,1)) as Note into #temp from cte c
left outer join AutoNumbers A on substring(@str,C.Number,1) = A.AutoNumber
select replace( (select ',' + Note from #temp for xml path('')) ,',','')
end

sriv
Starting Member

11 Posts

Posted - 2009-04-07 : 10:29:18

This is excellent. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources