1> /* Advanced SQL Programming Fall 2001*/
2> /* */
3> /* Take-home exercises (9/23): More on string manipulation */
4> /* */
5> use evelyn
1> /* */
2> /* Sort alphanumeric part numbers by letter first, then by num. */
3> /* (e.g., AC1, AC2, AC12, B1, B2, B11, CCC1, CCC2, CCC10) */
4> /* */
5> /* In my parts table, sorted by whole part number: */
6> select * from parts
7> order by part_id
part_id
--------
AC1
AC12
AC2
B1
B11
B2
C1
C100
C11
C12
C2
C20
CCC1
CCC10
CCC2
(15 rows affected)
1> /* */
2> /* (Note: In following query, no need to select the substrings */
3> /* - could have used directly in the order by clause.) */
/* Comments: patindex() finds where number portion starts; */
/* first substring returns character portion only; second */
/* substring, converted to integer, returns "numbers" only. */
4> /* */
5> select part_id,
6> substring(part_id, 1, patindex('%[1-9]%', part_id)-1),
7> convert(tinyint, substring(part_id, patindex('%[1-9]%', part_id),
8> char_length(part_id)))
9> from parts
10> order by 2, 3
part_id
-------- -------- ---
AC1 AC 1
AC2 AC 2
AC12 AC 12
B1 B 1
B2 B 2
B11 B 11
C1 C 1
C2 C 2
C11 C 11
C12 C 12
C20 C 20
C100 C 100
CCC1 CCC 1
CCC2 CCC 2
CCC10 CCC 10
(15 rows affected)
1> /* */
2> /* Proper case product names with & or three words. */
/* (Short comments start with --; longer ones between /* */ */
3> /* (Note: One- or two-word names removed in WHERE clause.) */
4> /* (Notice remaining problem with four-word name, not removed.)*/
5> /* */
6> select name,
7> -- uppercase first character
8> upper(substring(name, 1, 1))+
9> -- concat from 2nd char to first blank
10> substring(name, 2, charindex(" ", name)-1) +
11> -- concat uppercase first char after first blank
12> upper(substring(name, charindex(" ", name)+1, 1)) +
13> /*
14> find first blank in substring 2:
15> charindex(" ", substring(name, charindex(" ", name)+2,
16> char_length(name)))
17> */
18> -- concat from 2nd char after first blank to end of substring 2
19> substring(name, charindex(" ", name)+2,
20> charindex(" ", substring(name, charindex(" ", name)+2,
21> char_length(name)))) +
22> -- concat first char after blank in substring 2
23> upper(substring(substring(name, charindex(" ", name)+3,
24> char_length(name)),
25> charindex(" ", substring(name, charindex(" ", name)+2,
26> char_length(name))), 1)) +
27> -- build substring 3: from 2nd char after blank 2
28> substring (substring(name, charindex(" ", name) + 4,
29> char_length(name)),
30> charindex(" ", substring(name, charindex(" ", name)+2,
31> char_length(name))), char_length(name))
32> from product
33> where charindex(" ", name) > 0
34> and charindex
35> (" ", substring(name, charindex(" ", name)+1, char_length(name)))>0
name
--------------------
---------------------------------------------------------------
cook & book
Cook & Book
more paper dolls
More Paper Dolls
home poll kit
Home Poll Kit
teach yourself greek
Teach Yourself Greek
blood & guts
Blood & Guts
C++ for kids
C++ For Kids
how multi is media?
How Multi Is media?
(7 rows affected)
1> /* */
2> dump tran evelyn with no_log