- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
select model,
case when c1>='0' and c1<='9' then cast(c1 as integer) else 0 end +
case when c2>='0' and c2<='9' then cast(c2 as integer) else 0 end +
case when c3>='0' and c3<='9' then cast(c3 as integer) else 0 end +
case when c4>='0' and c4<='9' then cast(c4 as integer) else 0 end +
case when c5>='0' and c5<='9' then cast(c5 as integer) else 0 end +
case when c6>='0' and c6<='9' then cast(c6 as integer) else 0 end +
case when c7>='0' and c7<='9' then cast(c7 as integer) else 0 end +
case when c8>='0' and c8<='9' then cast(c8 as integer) else 0 end +
case when c9>='0' and c9<='9' then cast(c9 as integer) else 0 end +
case when c10>='0' and c10<='9' then cast(c10 as integer) else 0 end +
case when c11>='0' and c11<='9' then cast(c11 as integer) else 0 end +
case when c12>='0' and c12<='9' then cast(c12 as integer) else 0 end +
case when c13>='0' and c13<='9' then cast(c13 as integer) else 0 end +
case when c14>='0' and c14<='9' then cast(c14 as integer) else 0 end +
case when c15>='0' and c15<='9' then cast(c15 as integer) else 0 end +
case when c16>='0' and c16<='9' then cast(c16 as integer) else 0 end +
case when c17>='0' and c17<='9' then cast(c17 as integer) else 0 end +
case when c18>='0' and c18<='9' then cast(c18 as integer) else 0 end +
case when c19>='0' and c19<='9' then cast(c19 as integer) else 0 end +
case when c20>='0' and c20<='9' then cast(c20 as integer) else 0 end +
case when c21>='0' and c21<='9' then cast(c21 as integer) else 0 end +
case when c22>='0' and c22<='9' then cast(c22 as integer) else 0 end +
case when c23>='0' and c23<='9' then cast(c23 as integer) else 0 end +
case when c24>='0' and c24<='9' then cast(c24 as integer) else 0 end +
case when c25>='0' and c25<='9' then cast(c25 as integer) else 0 end +
case when c26>='0' and c26<='9' then cast(c26 as integer) else 0 end +
case when c27>='0' and c27<='9' then cast(c27 as integer) else 0 end +
case when c28>='0' and c28<='9' then cast(c28 as integer) else 0 end +
case when c29>='0' and c29<='9' then cast(c29 as integer) else 0 end +
case when c30>='0' and c30<='9' then cast(c30 as integer) else 0 end +
case when c21>='0' and c31<='9' then cast(c31 as integer) else 0 end +
case when c22>='0' and c32<='9' then cast(c32 as integer) else 0 end +
case when c23>='0' and c33<='9' then cast(c33 as integer) else 0 end +
case when c34>='0' and c34<='9' then cast(c34 as integer) else 0 end +
case when c35>='0' and c35<='9' then cast(c35 as integer) else 0 end +
case when c36>='0' and c36<='9' then cast(c36 as integer) else 0 end +
case when c37>='0' and c37<='9' then cast(c37 as integer) else 0 end +
case when c38>='0' and c38<='9' then cast(c38 as integer) else 0 end +
case when c39>='0' and c39<='9' then cast(c39 as integer) else 0 end +
case when c40>='0' and c40<='9' then cast(c40 as integer) else 0 end +
case when c41>='0' and c41<='9' then cast(c41 as integer) else 0 end +
case when c42>='0' and c42<='9' then cast(c42 as integer) else 0 end +
case when c43>='0' and c43<='9' then cast(c43 as integer) else 0 end +
case when c44>='0' and c44<='9' then cast(c44 as integer) else 0 end +
case when c45>='0' and c45<='9' then cast(c45 as integer) else 0 end +
case when c46>='0' and c46<='9' then cast(c46 as integer) else 0 end +
case when c47>='0' and c47<='9' then cast(c47 as integer) else 0 end +
case when c48>='0' and c48<='9' then cast(c48 as integer) else 0 end +
case when c49>='0' and c49<='9' then cast(c49 as integer) else 0 end +
case when c50>='0' and c50<='9' then cast(c50 as integer) else 0 end from(
select model,
coalesce(substring(m,1,1),'0') as c1,
coalesce(substring(m,2,1),'0') as c2,
coalesce(substring(m,3,1),'0') as c3,
coalesce(substring(m,4,1),'0') as c4,
coalesce(substring(m,5,1),'0') as c5,
coalesce(substring(m,6,1),'0') as c6,
coalesce(substring(m,7,1),'0') as c7,
coalesce(substring(m,8,1),'0') as c8,
coalesce(substring(m,9,1),'0') as c9,
coalesce(substring(m,10,1),'0') as c10,
coalesce(substring(m,11,1),'0') as c11,
coalesce(substring(m,12,1),'0') as c12,
coalesce(substring(m,13,1),'0') as c13,
coalesce(substring(m,14,1),'0') as c14,
coalesce(substring(m,15,1),'0') as c15,
coalesce(substring(m,16,1),'0') as c16,
coalesce(substring(m,17,1),'0') as c17,
coalesce(substring(m,18,1),'0') as c18,
coalesce(substring(m,19,1),'0') as c19,
coalesce(substring(m,20,1),'0') as c20,
coalesce(substring(m,21,1),'0') as c21,
coalesce(substring(m,22,1),'0') as c22,
coalesce(substring(m,23,1),'0') as c23,
coalesce(substring(m,24,1),'0') as c24,
coalesce(substring(m,25,1),'0') as c25,
coalesce(substring(m,26,1),'0') as c26,
coalesce(substring(m,27,1),'0') as c27,
coalesce(substring(m,28,1),'0') as c28,
coalesce(substring(m,29,1),'0') as c29,
coalesce(substring(m,30,1),'0') as c30,
coalesce(substring(m,31,1),'0') as c31,
coalesce(substring(m,32,1),'0') as c32,
coalesce(substring(m,33,1),'0') as c33,
coalesce(substring(m,34,1),'0') as c34,
coalesce(substring(m,35,1),'0') as c35,
coalesce(substring(m,36,1),'0') as c36,
coalesce(substring(m,37,1),'0') as c37,
coalesce(substring(m,38,1),'0') as c38,
coalesce(substring(m,39,1),'0') as c39,
coalesce(substring(m,40,1),'0') as c40,
coalesce(substring(m,41,1),'0') as c41,
coalesce(substring(m,42,1),'0') as c42,
coalesce(substring(m,43,1),'0') as c43,
coalesce(substring(m,44,1),'0') as c44,
coalesce(substring(m,45,1),'0') as c45,
coalesce(substring(m,46,1),'0') as c46,
coalesce(substring(m,47,1),'0') as c47,
Один из вариантов решения задачи с форума сайта sql-ex (может стебный):
Посчитать сумму цифр в номере каждой модели из таблицы Product
Вывод: номер модели, сумма цифр
Полный код все равно не помещается, значит его нельзя выдать за свой.
Follow us!