You're here: Snippet Directory » Databases (26)
Language:

Predeccesor and successor detection in different-collation s

Language: English
Programming Language: SQL
Published by: vnrg [not registered]
Last Update: 5/15/2006
Views: 943


Description

Predeccesor and successor detection in different-collation supporting db systems.

Code

1 /* 2 3 Predeccesor and successor detection in different-collation supporting db systems. 4 5 by v.nrg (energized.sk/~vnrg) 6 7 notes: 8 9 Designed for MSSQL2K but apliable (and maybe much easier) on any rdbms. 10 */ 11 12 drop table collrule 13 go 14 create table collrule (c smallint IDENTITY (0, 1) not null, w smallint) 15 go 16 -- generate progression (single byte character use) 17 declare @i int; set @i=0; while @i<=255 begin insert collrule(w) values(@i); set @i=@i+1; end; 18 go 19 20 21 -- performs collation tests and computes character weight (in condition that if a < b < c then a < c) 22 update collrule 23 set w = x.w 24 from ( 25 select a.c, count (*) - 1 as w 26 from 27 collrule a inner join collrule b 28 on (char(a.c) >= char (b.c)) 29 group by a.c 30 ) x 31 where collrule.c = x.c 32 go 33 34 35 36 37 --EXAMPLES--------------------------------------------------------- 38 39 40 41 -- 1 42 -- assign successor to every character code (NULL if last one) 43 select a.c, b.c as [next] 44 from 45 collrule a left join collrule b 46 on (a.w + 1 = b.w) 47 order by a.c 48 49 /* 50 eof 51 */

No comments avaiable

Add a comment

Name *  

Email (won't be displayed) *    

Website  

Comment *  

Sicherheitscode Security Code *    

RSS