October 20, 2016 at 4:02 pm
Hi All,
I need help with sql to parsing string.
Ex. ''XDX114A77400' or '1123AB00171'
I live to parse out any character to 1 column, and any number to another column.
Letter Number
XDXA 11477400
AB 112300171
Please help and thanks a lot in advance.
Minh
October 20, 2016 at 8:03 pm
The most efficient way I've found, when you know the column maximum length (and it's not thousands, and it isn't going to change[!]) is simply hard-coding a case statement for each index of the string column
select mytable.text
, case when substring(mytable.text, 1, 1) like '[0-9]' then substring(mytable.text, 1, 1) else '' end
+ case when substring(mytable.text, 2, 1) like '[0-9]' then substring(mytable.text, 2, 1) else '' end
+ case when substring(mytable.text, 3, 1) like '[0-9]' then substring(mytable.text, 3, 1) else '' end
+ case when substring(mytable.text, 4, 1) like '[0-9]' then substring(mytable.text, 4, 1) else '' end
+ case when substring(mytable.text, 5, 1) like '[0-9]' then substring(mytable.text, 5, 1) else '' end
+ case when substring(mytable.text, 6, 1) like '[0-9]' then substring(mytable.text, 6, 1) else '' end
+ case when substring(mytable.text, 7, 1) like '[0-9]' then substring(mytable.text, 7, 1) else '' end
+ case when substring(mytable.text, 8, 1) like '[0-9]' then substring(mytable.text, 8, 1) else '' end
+ case when substring(mytable.text, 9, 1) like '[0-9]' then substring(mytable.text, 9, 1) else '' end
+ case when substring(mytable.text, 10, 1) like '[0-9]' then substring(mytable.text, 10, 1) else '' end
+ case when substring(mytable.text, 11, 1) like '[0-9]' then substring(mytable.text, 11, 1) else '' end
+ case when substring(mytable.text, 12, 1) like '[0-9]' then substring(mytable.text, 12, 1) else '' end
+ case when substring(mytable.text, 13, 1) like '[0-9]' then substring(mytable.text, 13, 1) else '' end
+ case when substring(mytable.text, 14, 1) like '[0-9]' then substring(mytable.text, 14, 1) else '' end
+ case when substring(mytable.text, 15, 1) like '[0-9]' then substring(mytable.text, 15, 1) else '' end
+ case when substring(mytable.text, 16, 1) like '[0-9]' then substring(mytable.text, 16, 1) else '' end
+ case when substring(mytable.text, 17, 1) like '[0-9]' then substring(mytable.text, 17, 1) else '' end
+ case when substring(mytable.text, 18, 1) like '[0-9]' then substring(mytable.text, 18, 1) else '' end
+ case when substring(mytable.text, 19, 1) like '[0-9]' then substring(mytable.text, 19, 1) else '' end
+ case when substring(mytable.text, 20, 1) like '[0-9]' then substring(mytable.text, 20, 1) else '' end
+ case when substring(mytable.text, 21, 1) like '[0-9]' then substring(mytable.text, 21, 1) else '' end
+ case when substring(mytable.text, 22, 1) like '[0-9]' then substring(mytable.text, 22, 1) else '' end
+ case when substring(mytable.text, 23, 1) like '[0-9]' then substring(mytable.text, 23, 1) else '' end
+ case when substring(mytable.text, 24, 1) like '[0-9]' then substring(mytable.text, 24, 1) else '' end
+ case when substring(mytable.text, 25, 1) like '[0-9]' then substring(mytable.text, 25, 1) else '' end
+ case when substring(mytable.text, 26, 1) like '[0-9]' then substring(mytable.text, 26, 1) else '' end
+ case when substring(mytable.text, 27, 1) like '[0-9]' then substring(mytable.text, 27, 1) else '' end
+ case when substring(mytable.text, 28, 1) like '[0-9]' then substring(mytable.text, 28, 1) else '' end
+ case when substring(mytable.text, 29, 1) like '[0-9]' then substring(mytable.text, 29, 1) else '' end
+ case when substring(mytable.text, 30, 1) like '[0-9]' then substring(mytable.text, 30, 1) else '' end
+ case when substring(mytable.text, 31, 1) like '[0-9]' then substring(mytable.text, 31, 1) else '' end
+ case when substring(mytable.text, 32, 1) like '[0-9]' then substring(mytable.text, 32, 1) else '' end
+ case when substring(mytable.text, 33, 1) like '[0-9]' then substring(mytable.text, 33, 1) else '' end
+ case when substring(mytable.text, 34, 1) like '[0-9]' then substring(mytable.text, 34, 1) else '' end
+ case when substring(mytable.text, 35, 1) like '[0-9]' then substring(mytable.text, 35, 1) else '' end
+ case when substring(mytable.text, 36, 1) like '[0-9]' then substring(mytable.text, 36, 1) else '' end
+ case when substring(mytable.text, 37, 1) like '[0-9]' then substring(mytable.text, 37, 1) else '' end
+ case when substring(mytable.text, 38, 1) like '[0-9]' then substring(mytable.text, 38, 1) else '' end
+ case when substring(mytable.text, 39, 1) like '[0-9]' then substring(mytable.text, 39, 1) else '' end
+ case when substring(mytable.text, 40, 1) like '[0-9]' then substring(mytable.text, 40, 1) else '' end
+ case when substring(mytable.text, 41, 1) like '[0-9]' then substring(mytable.text, 41, 1) else '' end
+ case when substring(mytable.text, 42, 1) like '[0-9]' then substring(mytable.text, 42, 1) else '' end
+ case when substring(mytable.text, 43, 1) like '[0-9]' then substring(mytable.text, 43, 1) else '' end
+ case when substring(mytable.text, 44, 1) like '[0-9]' then substring(mytable.text, 44, 1) else '' end
+ case when substring(mytable.text, 45, 1) like '[0-9]' then substring(mytable.text, 45, 1) else '' end
+ case when substring(mytable.text, 46, 1) like '[0-9]' then substring(mytable.text, 46, 1) else '' end
+ case when substring(mytable.text, 47, 1) like '[0-9]' then substring(mytable.text, 47, 1) else '' end
+ case when substring(mytable.text, 48, 1) like '[0-9]' then substring(mytable.text, 48, 1) else '' end
+ case when substring(mytable.text, 49, 1) like '[0-9]' then substring(mytable.text, 49, 1) else '' end
+ case when substring(mytable.text, 50, 1) like '[0-9]' then substring(mytable.text, 50, 1) else '' end
+ case when substring(mytable.text, 51, 1) like '[0-9]' then substring(mytable.text, 51, 1) else '' end
+ case when substring(mytable.text, 52, 1) like '[0-9]' then substring(mytable.text, 52, 1) else '' end
+ case when substring(mytable.text, 53, 1) like '[0-9]' then substring(mytable.text, 53, 1) else '' end
+ case when substring(mytable.text, 54, 1) like '[0-9]' then substring(mytable.text, 54, 1) else '' end
+ case when substring(mytable.text, 55, 1) like '[0-9]' then substring(mytable.text, 55, 1) else '' end
+ case when substring(mytable.text, 56, 1) like '[0-9]' then substring(mytable.text, 56, 1) else '' end
+ case when substring(mytable.text, 57, 1) like '[0-9]' then substring(mytable.text, 57, 1) else '' end
+ case when substring(mytable.text, 58, 1) like '[0-9]' then substring(mytable.text, 58, 1) else '' end
+ case when substring(mytable.text, 59, 1) like '[0-9]' then substring(mytable.text, 59, 1) else '' end
+ case when substring(mytable.text, 60, 1) like '[0-9]' then substring(mytable.text, 60, 1) else '' end
+ case when substring(mytable.text, 61, 1) like '[0-9]' then substring(mytable.text, 61, 1) else '' end
+ case when substring(mytable.text, 62, 1) like '[0-9]' then substring(mytable.text, 62, 1) else '' end
+ case when substring(mytable.text, 63, 1) like '[0-9]' then substring(mytable.text, 63, 1) else '' end
+ case when substring(mytable.text, 64, 1) like '[0-9]' then substring(mytable.text, 64, 1) else '' end
+ case when substring(mytable.text, 65, 1) like '[0-9]' then substring(mytable.text, 65, 1) else '' end
+ case when substring(mytable.text, 66, 1) like '[0-9]' then substring(mytable.text, 66, 1) else '' end
+ case when substring(mytable.text, 67, 1) like '[0-9]' then substring(mytable.text, 67, 1) else '' end
+ case when substring(mytable.text, 68, 1) like '[0-9]' then substring(mytable.text, 68, 1) else '' end
+ case when substring(mytable.text, 69, 1) like '[0-9]' then substring(mytable.text, 69, 1) else '' end
+ case when substring(mytable.text, 70, 1) like '[0-9]' then substring(mytable.text, 70, 1) else '' end
+ case when substring(mytable.text, 71, 1) like '[0-9]' then substring(mytable.text, 71, 1) else '' end
+ case when substring(mytable.text, 72, 1) like '[0-9]' then substring(mytable.text, 72, 1) else '' end
+ case when substring(mytable.text, 73, 1) like '[0-9]' then substring(mytable.text, 73, 1) else '' end
+ case when substring(mytable.text, 74, 1) like '[0-9]' then substring(mytable.text, 74, 1) else '' end
+ case when substring(mytable.text, 75, 1) like '[0-9]' then substring(mytable.text, 75, 1) else '' end
+ case when substring(mytable.text, 76, 1) like '[0-9]' then substring(mytable.text, 76, 1) else '' end
+ case when substring(mytable.text, 77, 1) like '[0-9]' then substring(mytable.text, 77, 1) else '' end
+ case when substring(mytable.text, 78, 1) like '[0-9]' then substring(mytable.text, 78, 1) else '' end
+ case when substring(mytable.text, 79, 1) like '[0-9]' then substring(mytable.text, 79, 1) else '' end
+ case when substring(mytable.text, 80, 1) like '[0-9]' then substring(mytable.text, 80, 1) else '' end
+ case when substring(mytable.text, 81, 1) like '[0-9]' then substring(mytable.text, 81, 1) else '' end
+ case when substring(mytable.text, 82, 1) like '[0-9]' then substring(mytable.text, 82, 1) else '' end
+ case when substring(mytable.text, 83, 1) like '[0-9]' then substring(mytable.text, 83, 1) else '' end
+ case when substring(mytable.text, 84, 1) like '[0-9]' then substring(mytable.text, 84, 1) else '' end
+ case when substring(mytable.text, 85, 1) like '[0-9]' then substring(mytable.text, 85, 1) else '' end
+ case when substring(mytable.text, 86, 1) like '[0-9]' then substring(mytable.text, 86, 1) else '' end
+ case when substring(mytable.text, 87, 1) like '[0-9]' then substring(mytable.text, 87, 1) else '' end
+ case when substring(mytable.text, 88, 1) like '[0-9]' then substring(mytable.text, 88, 1) else '' end
+ case when substring(mytable.text, 89, 1) like '[0-9]' then substring(mytable.text, 89, 1) else '' end
+ case when substring(mytable.text, 90, 1) like '[0-9]' then substring(mytable.text, 90, 1) else '' end
+ case when substring(mytable.text, 91, 1) like '[0-9]' then substring(mytable.text, 91, 1) else '' end
+ case when substring(mytable.text, 92, 1) like '[0-9]' then substring(mytable.text, 92, 1) else '' end
+ case when substring(mytable.text, 93, 1) like '[0-9]' then substring(mytable.text, 93, 1) else '' end
+ case when substring(mytable.text, 94, 1) like '[0-9]' then substring(mytable.text, 94, 1) else '' end
+ case when substring(mytable.text, 95, 1) like '[0-9]' then substring(mytable.text, 95, 1) else '' end
+ case when substring(mytable.text, 96, 1) like '[0-9]' then substring(mytable.text, 96, 1) else '' end
+ case when substring(mytable.text, 97, 1) like '[0-9]' then substring(mytable.text, 97, 1) else '' end
+ case when substring(mytable.text, 98, 1) like '[0-9]' then substring(mytable.text, 98, 1) else '' end
+ case when substring(mytable.text, 99, 1) like '[0-9]' then substring(mytable.text, 99, 1) else '' end
+ case when substring(mytable.text, 100, 1) like '[0-9]' then substring(mytable.text, 100, 1) else '' end
+ case when substring(mytable.text, 101, 1) like '[0-9]' then substring(mytable.text, 101, 1) else '' end
+ case when substring(mytable.text, 102, 1) like '[0-9]' then substring(mytable.text, 102, 1) else '' end
+ case when substring(mytable.text, 103, 1) like '[0-9]' then substring(mytable.text, 103, 1) else '' end
+ case when substring(mytable.text, 104, 1) like '[0-9]' then substring(mytable.text, 104, 1) else '' end
+ case when substring(mytable.text, 105, 1) like '[0-9]' then substring(mytable.text, 105, 1) else '' end
+ case when substring(mytable.text, 106, 1) like '[0-9]' then substring(mytable.text, 106, 1) else '' end
+ case when substring(mytable.text, 107, 1) like '[0-9]' then substring(mytable.text, 107, 1) else '' end
+ case when substring(mytable.text, 108, 1) like '[0-9]' then substring(mytable.text, 108, 1) else '' end
+ case when substring(mytable.text, 109, 1) like '[0-9]' then substring(mytable.text, 109, 1) else '' end
+ case when substring(mytable.text, 110, 1) like '[0-9]' then substring(mytable.text, 110, 1) else '' end
+ case when substring(mytable.text, 111, 1) like '[0-9]' then substring(mytable.text, 111, 1) else '' end
+ case when substring(mytable.text, 112, 1) like '[0-9]' then substring(mytable.text, 112, 1) else '' end
+ case when substring(mytable.text, 113, 1) like '[0-9]' then substring(mytable.text, 113, 1) else '' end
+ case when substring(mytable.text, 114, 1) like '[0-9]' then substring(mytable.text, 114, 1) else '' end
+ case when substring(mytable.text, 115, 1) like '[0-9]' then substring(mytable.text, 115, 1) else '' end
+ case when substring(mytable.text, 116, 1) like '[0-9]' then substring(mytable.text, 116, 1) else '' end
+ case when substring(mytable.text, 117, 1) like '[0-9]' then substring(mytable.text, 117, 1) else '' end
+ case when substring(mytable.text, 118, 1) like '[0-9]' then substring(mytable.text, 118, 1) else '' end
+ case when substring(mytable.text, 119, 1) like '[0-9]' then substring(mytable.text, 119, 1) else '' end
+ case when substring(mytable.text, 120, 1) like '[0-9]' then substring(mytable.text, 120, 1) else '' end
+ case when substring(mytable.text, 121, 1) like '[0-9]' then substring(mytable.text, 121, 1) else '' end
+ case when substring(mytable.text, 122, 1) like '[0-9]' then substring(mytable.text, 122, 1) else '' end
+ case when substring(mytable.text, 123, 1) like '[0-9]' then substring(mytable.text, 123, 1) else '' end
+ case when substring(mytable.text, 124, 1) like '[0-9]' then substring(mytable.text, 124, 1) else '' end
+ case when substring(mytable.text, 125, 1) like '[0-9]' then substring(mytable.text, 125, 1) else '' end
+ case when substring(mytable.text, 126, 1) like '[0-9]' then substring(mytable.text, 126, 1) else '' end
+ case when substring(mytable.text, 127, 1) like '[0-9]' then substring(mytable.text, 127, 1) else '' end
+ case when substring(mytable.text, 128, 1) like '[0-9]' then substring(mytable.text, 128, 1) else '' end
+ case when substring(mytable.text, 129, 1) like '[0-9]' then substring(mytable.text, 129, 1) else '' end
+ case when substring(mytable.text, 130, 1) like '[0-9]' then substring(mytable.text, 130, 1) else '' end
+ case when substring(mytable.text, 131, 1) like '[0-9]' then substring(mytable.text, 131, 1) else '' end
+ case when substring(mytable.text, 132, 1) like '[0-9]' then substring(mytable.text, 132, 1) else '' end
+ case when substring(mytable.text, 133, 1) like '[0-9]' then substring(mytable.text, 133, 1) else '' end
+ case when substring(mytable.text, 134, 1) like '[0-9]' then substring(mytable.text, 134, 1) else '' end
+ case when substring(mytable.text, 135, 1) like '[0-9]' then substring(mytable.text, 135, 1) else '' end
+ case when substring(mytable.text, 136, 1) like '[0-9]' then substring(mytable.text, 136, 1) else '' end
+ case when substring(mytable.text, 137, 1) like '[0-9]' then substring(mytable.text, 137, 1) else '' end
+ case when substring(mytable.text, 138, 1) like '[0-9]' then substring(mytable.text, 138, 1) else '' end
+ case when substring(mytable.text, 139, 1) like '[0-9]' then substring(mytable.text, 139, 1) else '' end
+ case when substring(mytable.text, 140, 1) like '[0-9]' then substring(mytable.text, 140, 1) else '' end
+ case when substring(mytable.text, 141, 1) like '[0-9]' then substring(mytable.text, 141, 1) else '' end
+ case when substring(mytable.text, 142, 1) like '[0-9]' then substring(mytable.text, 142, 1) else '' end
+ case when substring(mytable.text, 143, 1) like '[0-9]' then substring(mytable.text, 143, 1) else '' end
+ case when substring(mytable.text, 144, 1) like '[0-9]' then substring(mytable.text, 144, 1) else '' end
+ case when substring(mytable.text, 145, 1) like '[0-9]' then substring(mytable.text, 145, 1) else '' end
+ case when substring(mytable.text, 146, 1) like '[0-9]' then substring(mytable.text, 146, 1) else '' end
+ case when substring(mytable.text, 147, 1) like '[0-9]' then substring(mytable.text, 147, 1) else '' end
+ case when substring(mytable.text, 148, 1) like '[0-9]' then substring(mytable.text, 148, 1) else '' end
+ case when substring(mytable.text, 149, 1) like '[0-9]' then substring(mytable.text, 149, 1) else '' end
+ case when substring(mytable.text, 150, 1) like '[0-9]' then substring(mytable.text, 150, 1) else '' end
+ case when substring(mytable.text, 151, 1) like '[0-9]' then substring(mytable.text, 151, 1) else '' end
+ case when substring(mytable.text, 152, 1) like '[0-9]' then substring(mytable.text, 152, 1) else '' end
+ case when substring(mytable.text, 153, 1) like '[0-9]' then substring(mytable.text, 153, 1) else '' end
+ case when substring(mytable.text, 154, 1) like '[0-9]' then substring(mytable.text, 154, 1) else '' end
+ case when substring(mytable.text, 155, 1) like '[0-9]' then substring(mytable.text, 155, 1) else '' end
+ case when substring(mytable.text, 156, 1) like '[0-9]' then substring(mytable.text, 156, 1) else '' end
+ case when substring(mytable.text, 157, 1) like '[0-9]' then substring(mytable.text, 157, 1) else '' end
+ case when substring(mytable.text, 158, 1) like '[0-9]' then substring(mytable.text, 158, 1) else '' end
+ case when substring(mytable.text, 159, 1) like '[0-9]' then substring(mytable.text, 159, 1) else '' end
+ case when substring(mytable.text, 160, 1) like '[0-9]' then substring(mytable.text, 160, 1) else '' end
+ case when substring(mytable.text, 161, 1) like '[0-9]' then substring(mytable.text, 161, 1) else '' end
+ case when substring(mytable.text, 162, 1) like '[0-9]' then substring(mytable.text, 162, 1) else '' end
+ case when substring(mytable.text, 163, 1) like '[0-9]' then substring(mytable.text, 163, 1) else '' end
+ case when substring(mytable.text, 164, 1) like '[0-9]' then substring(mytable.text, 164, 1) else '' end
+ case when substring(mytable.text, 165, 1) like '[0-9]' then substring(mytable.text, 165, 1) else '' end
+ case when substring(mytable.text, 166, 1) like '[0-9]' then substring(mytable.text, 166, 1) else '' end
+ case when substring(mytable.text, 167, 1) like '[0-9]' then substring(mytable.text, 167, 1) else '' end
+ case when substring(mytable.text, 168, 1) like '[0-9]' then substring(mytable.text, 168, 1) else '' end
+ case when substring(mytable.text, 169, 1) like '[0-9]' then substring(mytable.text, 169, 1) else '' end
+ case when substring(mytable.text, 170, 1) like '[0-9]' then substring(mytable.text, 170, 1) else '' end
+ case when substring(mytable.text, 171, 1) like '[0-9]' then substring(mytable.text, 171, 1) else '' end
+ case when substring(mytable.text, 172, 1) like '[0-9]' then substring(mytable.text, 172, 1) else '' end
+ case when substring(mytable.text, 173, 1) like '[0-9]' then substring(mytable.text, 173, 1) else '' end
+ case when substring(mytable.text, 174, 1) like '[0-9]' then substring(mytable.text, 174, 1) else '' end
+ case when substring(mytable.text, 175, 1) like '[0-9]' then substring(mytable.text, 175, 1) else '' end
+ case when substring(mytable.text, 176, 1) like '[0-9]' then substring(mytable.text, 176, 1) else '' end
+ case when substring(mytable.text, 177, 1) like '[0-9]' then substring(mytable.text, 177, 1) else '' end
+ case when substring(mytable.text, 178, 1) like '[0-9]' then substring(mytable.text, 178, 1) else '' end
+ case when substring(mytable.text, 179, 1) like '[0-9]' then substring(mytable.text, 179, 1) else '' end
+ case when substring(mytable.text, 180, 1) like '[0-9]' then substring(mytable.text, 180, 1) else '' end
+ case when substring(mytable.text, 181, 1) like '[0-9]' then substring(mytable.text, 181, 1) else '' end
+ case when substring(mytable.text, 182, 1) like '[0-9]' then substring(mytable.text, 182, 1) else '' end
+ case when substring(mytable.text, 183, 1) like '[0-9]' then substring(mytable.text, 183, 1) else '' end
+ case when substring(mytable.text, 184, 1) like '[0-9]' then substring(mytable.text, 184, 1) else '' end
+ case when substring(mytable.text, 185, 1) like '[0-9]' then substring(mytable.text, 185, 1) else '' end
+ case when substring(mytable.text, 186, 1) like '[0-9]' then substring(mytable.text, 186, 1) else '' end
+ case when substring(mytable.text, 187, 1) like '[0-9]' then substring(mytable.text, 187, 1) else '' end
+ case when substring(mytable.text, 188, 1) like '[0-9]' then substring(mytable.text, 188, 1) else '' end
+ case when substring(mytable.text, 189, 1) like '[0-9]' then substring(mytable.text, 189, 1) else '' end
+ case when substring(mytable.text, 190, 1) like '[0-9]' then substring(mytable.text, 190, 1) else '' end
+ case when substring(mytable.text, 191, 1) like '[0-9]' then substring(mytable.text, 191, 1) else '' end
+ case when substring(mytable.text, 192, 1) like '[0-9]' then substring(mytable.text, 192, 1) else '' end
+ case when substring(mytable.text, 193, 1) like '[0-9]' then substring(mytable.text, 193, 1) else '' end
+ case when substring(mytable.text, 194, 1) like '[0-9]' then substring(mytable.text, 194, 1) else '' end
+ case when substring(mytable.text, 195, 1) like '[0-9]' then substring(mytable.text, 195, 1) else '' end
+ case when substring(mytable.text, 196, 1) like '[0-9]' then substring(mytable.text, 196, 1) else '' end
+ case when substring(mytable.text, 197, 1) like '[0-9]' then substring(mytable.text, 197, 1) else '' end
+ case when substring(mytable.text, 198, 1) like '[0-9]' then substring(mytable.text, 198, 1) else '' end
+ case when substring(mytable.text, 199, 1) like '[0-9]' then substring(mytable.text, 199, 1) else '' end
+ case when substring(mytable.text, 200, 1) like '[0-9]' then substring(mytable.text, 200, 1) else '' end as ints
, case when substring(mytable.text, 1, 1) not like '[0-9]' then substring(mytable.text, 1, 1) else '' end
+ case when substring(mytable.text, 2, 1) not like '[0-9]' then substring(mytable.text, 2, 1) else '' end
+ case when substring(mytable.text, 3, 1) not like '[0-9]' then substring(mytable.text, 3, 1) else '' end
+ case when substring(mytable.text, 4, 1) not like '[0-9]' then substring(mytable.text, 4, 1) else '' end
+ case when substring(mytable.text, 5, 1) not like '[0-9]' then substring(mytable.text, 5, 1) else '' end
+ case when substring(mytable.text, 6, 1) not like '[0-9]' then substring(mytable.text, 6, 1) else '' end
+ case when substring(mytable.text, 7, 1) not like '[0-9]' then substring(mytable.text, 7, 1) else '' end
+ case when substring(mytable.text, 8, 1) not like '[0-9]' then substring(mytable.text, 8, 1) else '' end
+ case when substring(mytable.text, 9, 1) not like '[0-9]' then substring(mytable.text, 9, 1) else '' end
+ case when substring(mytable.text, 10, 1) not like '[0-9]' then substring(mytable.text, 10, 1) else '' end
+ case when substring(mytable.text, 11, 1) not like '[0-9]' then substring(mytable.text, 11, 1) else '' end
+ case when substring(mytable.text, 12, 1) not like '[0-9]' then substring(mytable.text, 12, 1) else '' end
+ case when substring(mytable.text, 13, 1) not like '[0-9]' then substring(mytable.text, 13, 1) else '' end
+ case when substring(mytable.text, 14, 1) not like '[0-9]' then substring(mytable.text, 14, 1) else '' end
+ case when substring(mytable.text, 15, 1) not like '[0-9]' then substring(mytable.text, 15, 1) else '' end
+ case when substring(mytable.text, 16, 1) not like '[0-9]' then substring(mytable.text, 16, 1) else '' end
+ case when substring(mytable.text, 17, 1) not like '[0-9]' then substring(mytable.text, 17, 1) else '' end
+ case when substring(mytable.text, 18, 1) not like '[0-9]' then substring(mytable.text, 18, 1) else '' end
+ case when substring(mytable.text, 19, 1) not like '[0-9]' then substring(mytable.text, 19, 1) else '' end
+ case when substring(mytable.text, 20, 1) not like '[0-9]' then substring(mytable.text, 20, 1) else '' end
+ case when substring(mytable.text, 21, 1) not like '[0-9]' then substring(mytable.text, 21, 1) else '' end
+ case when substring(mytable.text, 22, 1) not like '[0-9]' then substring(mytable.text, 22, 1) else '' end
+ case when substring(mytable.text, 23, 1) not like '[0-9]' then substring(mytable.text, 23, 1) else '' end
+ case when substring(mytable.text, 24, 1) not like '[0-9]' then substring(mytable.text, 24, 1) else '' end
+ case when substring(mytable.text, 25, 1) not like '[0-9]' then substring(mytable.text, 25, 1) else '' end
+ case when substring(mytable.text, 26, 1) not like '[0-9]' then substring(mytable.text, 26, 1) else '' end
+ case when substring(mytable.text, 27, 1) not like '[0-9]' then substring(mytable.text, 27, 1) else '' end
+ case when substring(mytable.text, 28, 1) not like '[0-9]' then substring(mytable.text, 28, 1) else '' end
+ case when substring(mytable.text, 29, 1) not like '[0-9]' then substring(mytable.text, 29, 1) else '' end
+ case when substring(mytable.text, 30, 1) not like '[0-9]' then substring(mytable.text, 30, 1) else '' end
+ case when substring(mytable.text, 31, 1) not like '[0-9]' then substring(mytable.text, 31, 1) else '' end
+ case when substring(mytable.text, 32, 1) not like '[0-9]' then substring(mytable.text, 32, 1) else '' end
+ case when substring(mytable.text, 33, 1) not like '[0-9]' then substring(mytable.text, 33, 1) else '' end
+ case when substring(mytable.text, 34, 1) not like '[0-9]' then substring(mytable.text, 34, 1) else '' end
+ case when substring(mytable.text, 35, 1) not like '[0-9]' then substring(mytable.text, 35, 1) else '' end
+ case when substring(mytable.text, 36, 1) not like '[0-9]' then substring(mytable.text, 36, 1) else '' end
+ case when substring(mytable.text, 37, 1) not like '[0-9]' then substring(mytable.text, 37, 1) else '' end
+ case when substring(mytable.text, 38, 1) not like '[0-9]' then substring(mytable.text, 38, 1) else '' end
+ case when substring(mytable.text, 39, 1) not like '[0-9]' then substring(mytable.text, 39, 1) else '' end
+ case when substring(mytable.text, 40, 1) not like '[0-9]' then substring(mytable.text, 40, 1) else '' end
+ case when substring(mytable.text, 41, 1) not like '[0-9]' then substring(mytable.text, 41, 1) else '' end
+ case when substring(mytable.text, 42, 1) not like '[0-9]' then substring(mytable.text, 42, 1) else '' end
+ case when substring(mytable.text, 43, 1) not like '[0-9]' then substring(mytable.text, 43, 1) else '' end
+ case when substring(mytable.text, 44, 1) not like '[0-9]' then substring(mytable.text, 44, 1) else '' end
+ case when substring(mytable.text, 45, 1) not like '[0-9]' then substring(mytable.text, 45, 1) else '' end
+ case when substring(mytable.text, 46, 1) not like '[0-9]' then substring(mytable.text, 46, 1) else '' end
+ case when substring(mytable.text, 47, 1) not like '[0-9]' then substring(mytable.text, 47, 1) else '' end
+ case when substring(mytable.text, 48, 1) not like '[0-9]' then substring(mytable.text, 48, 1) else '' end
+ case when substring(mytable.text, 49, 1) not like '[0-9]' then substring(mytable.text, 49, 1) else '' end
+ case when substring(mytable.text, 50, 1) not like '[0-9]' then substring(mytable.text, 50, 1) else '' end
+ case when substring(mytable.text, 51, 1) not like '[0-9]' then substring(mytable.text, 51, 1) else '' end
+ case when substring(mytable.text, 52, 1) not like '[0-9]' then substring(mytable.text, 52, 1) else '' end
+ case when substring(mytable.text, 53, 1) not like '[0-9]' then substring(mytable.text, 53, 1) else '' end
+ case when substring(mytable.text, 54, 1) not like '[0-9]' then substring(mytable.text, 54, 1) else '' end
+ case when substring(mytable.text, 55, 1) not like '[0-9]' then substring(mytable.text, 55, 1) else '' end
+ case when substring(mytable.text, 56, 1) not like '[0-9]' then substring(mytable.text, 56, 1) else '' end
+ case when substring(mytable.text, 57, 1) not like '[0-9]' then substring(mytable.text, 57, 1) else '' end
+ case when substring(mytable.text, 58, 1) not like '[0-9]' then substring(mytable.text, 58, 1) else '' end
+ case when substring(mytable.text, 59, 1) not like '[0-9]' then substring(mytable.text, 59, 1) else '' end
+ case when substring(mytable.text, 60, 1) not like '[0-9]' then substring(mytable.text, 60, 1) else '' end
+ case when substring(mytable.text, 61, 1) not like '[0-9]' then substring(mytable.text, 61, 1) else '' end
+ case when substring(mytable.text, 62, 1) not like '[0-9]' then substring(mytable.text, 62, 1) else '' end
+ case when substring(mytable.text, 63, 1) not like '[0-9]' then substring(mytable.text, 63, 1) else '' end
+ case when substring(mytable.text, 64, 1) not like '[0-9]' then substring(mytable.text, 64, 1) else '' end
+ case when substring(mytable.text, 65, 1) not like '[0-9]' then substring(mytable.text, 65, 1) else '' end
+ case when substring(mytable.text, 66, 1) not like '[0-9]' then substring(mytable.text, 66, 1) else '' end
+ case when substring(mytable.text, 67, 1) not like '[0-9]' then substring(mytable.text, 67, 1) else '' end
+ case when substring(mytable.text, 68, 1) not like '[0-9]' then substring(mytable.text, 68, 1) else '' end
+ case when substring(mytable.text, 69, 1) not like '[0-9]' then substring(mytable.text, 69, 1) else '' end
+ case when substring(mytable.text, 70, 1) not like '[0-9]' then substring(mytable.text, 70, 1) else '' end
+ case when substring(mytable.text, 71, 1) not like '[0-9]' then substring(mytable.text, 71, 1) else '' end
+ case when substring(mytable.text, 72, 1) not like '[0-9]' then substring(mytable.text, 72, 1) else '' end
+ case when substring(mytable.text, 73, 1) not like '[0-9]' then substring(mytable.text, 73, 1) else '' end
+ case when substring(mytable.text, 74, 1) not like '[0-9]' then substring(mytable.text, 74, 1) else '' end
+ case when substring(mytable.text, 75, 1) not like '[0-9]' then substring(mytable.text, 75, 1) else '' end
+ case when substring(mytable.text, 76, 1) not like '[0-9]' then substring(mytable.text, 76, 1) else '' end
+ case when substring(mytable.text, 77, 1) not like '[0-9]' then substring(mytable.text, 77, 1) else '' end
+ case when substring(mytable.text, 78, 1) not like '[0-9]' then substring(mytable.text, 78, 1) else '' end
+ case when substring(mytable.text, 79, 1) not like '[0-9]' then substring(mytable.text, 79, 1) else '' end
+ case when substring(mytable.text, 80, 1) not like '[0-9]' then substring(mytable.text, 80, 1) else '' end
+ case when substring(mytable.text, 81, 1) not like '[0-9]' then substring(mytable.text, 81, 1) else '' end
+ case when substring(mytable.text, 82, 1) not like '[0-9]' then substring(mytable.text, 82, 1) else '' end
+ case when substring(mytable.text, 83, 1) not like '[0-9]' then substring(mytable.text, 83, 1) else '' end
+ case when substring(mytable.text, 84, 1) not like '[0-9]' then substring(mytable.text, 84, 1) else '' end
+ case when substring(mytable.text, 85, 1) not like '[0-9]' then substring(mytable.text, 85, 1) else '' end
+ case when substring(mytable.text, 86, 1) not like '[0-9]' then substring(mytable.text, 86, 1) else '' end
+ case when substring(mytable.text, 87, 1) not like '[0-9]' then substring(mytable.text, 87, 1) else '' end
+ case when substring(mytable.text, 88, 1) not like '[0-9]' then substring(mytable.text, 88, 1) else '' end
+ case when substring(mytable.text, 89, 1) not like '[0-9]' then substring(mytable.text, 89, 1) else '' end
+ case when substring(mytable.text, 90, 1) not like '[0-9]' then substring(mytable.text, 90, 1) else '' end
+ case when substring(mytable.text, 91, 1) not like '[0-9]' then substring(mytable.text, 91, 1) else '' end
+ case when substring(mytable.text, 92, 1) not like '[0-9]' then substring(mytable.text, 92, 1) else '' end
+ case when substring(mytable.text, 93, 1) not like '[0-9]' then substring(mytable.text, 93, 1) else '' end
+ case when substring(mytable.text, 94, 1) not like '[0-9]' then substring(mytable.text, 94, 1) else '' end
+ case when substring(mytable.text, 95, 1) not like '[0-9]' then substring(mytable.text, 95, 1) else '' end
+ case when substring(mytable.text, 96, 1) not like '[0-9]' then substring(mytable.text, 96, 1) else '' end
+ case when substring(mytable.text, 97, 1) not like '[0-9]' then substring(mytable.text, 97, 1) else '' end
+ case when substring(mytable.text, 98, 1) not like '[0-9]' then substring(mytable.text, 98, 1) else '' end
+ case when substring(mytable.text, 99, 1) not like '[0-9]' then substring(mytable.text, 99, 1) else '' end
+ case when substring(mytable.text, 100, 1) not like '[0-9]' then substring(mytable.text, 100, 1) else '' end
+ case when substring(mytable.text, 101, 1) not like '[0-9]' then substring(mytable.text, 101, 1) else '' end
+ case when substring(mytable.text, 102, 1) not like '[0-9]' then substring(mytable.text, 102, 1) else '' end
+ case when substring(mytable.text, 103, 1) not like '[0-9]' then substring(mytable.text, 103, 1) else '' end
+ case when substring(mytable.text, 104, 1) not like '[0-9]' then substring(mytable.text, 104, 1) else '' end
+ case when substring(mytable.text, 105, 1) not like '[0-9]' then substring(mytable.text, 105, 1) else '' end
+ case when substring(mytable.text, 106, 1) not like '[0-9]' then substring(mytable.text, 106, 1) else '' end
+ case when substring(mytable.text, 107, 1) not like '[0-9]' then substring(mytable.text, 107, 1) else '' end
+ case when substring(mytable.text, 108, 1) not like '[0-9]' then substring(mytable.text, 108, 1) else '' end
+ case when substring(mytable.text, 109, 1) not like '[0-9]' then substring(mytable.text, 109, 1) else '' end
+ case when substring(mytable.text, 110, 1) not like '[0-9]' then substring(mytable.text, 110, 1) else '' end
+ case when substring(mytable.text, 111, 1) not like '[0-9]' then substring(mytable.text, 111, 1) else '' end
+ case when substring(mytable.text, 112, 1) not like '[0-9]' then substring(mytable.text, 112, 1) else '' end
+ case when substring(mytable.text, 113, 1) not like '[0-9]' then substring(mytable.text, 113, 1) else '' end
+ case when substring(mytable.text, 114, 1) not like '[0-9]' then substring(mytable.text, 114, 1) else '' end
+ case when substring(mytable.text, 115, 1) not like '[0-9]' then substring(mytable.text, 115, 1) else '' end
+ case when substring(mytable.text, 116, 1) not like '[0-9]' then substring(mytable.text, 116, 1) else '' end
+ case when substring(mytable.text, 117, 1) not like '[0-9]' then substring(mytable.text, 117, 1) else '' end
+ case when substring(mytable.text, 118, 1) not like '[0-9]' then substring(mytable.text, 118, 1) else '' end
+ case when substring(mytable.text, 119, 1) not like '[0-9]' then substring(mytable.text, 119, 1) else '' end
+ case when substring(mytable.text, 120, 1) not like '[0-9]' then substring(mytable.text, 120, 1) else '' end
+ case when substring(mytable.text, 121, 1) not like '[0-9]' then substring(mytable.text, 121, 1) else '' end
+ case when substring(mytable.text, 122, 1) not like '[0-9]' then substring(mytable.text, 122, 1) else '' end
+ case when substring(mytable.text, 123, 1) not like '[0-9]' then substring(mytable.text, 123, 1) else '' end
+ case when substring(mytable.text, 124, 1) not like '[0-9]' then substring(mytable.text, 124, 1) else '' end
+ case when substring(mytable.text, 125, 1) not like '[0-9]' then substring(mytable.text, 125, 1) else '' end
+ case when substring(mytable.text, 126, 1) not like '[0-9]' then substring(mytable.text, 126, 1) else '' end
+ case when substring(mytable.text, 127, 1) not like '[0-9]' then substring(mytable.text, 127, 1) else '' end
+ case when substring(mytable.text, 128, 1) not like '[0-9]' then substring(mytable.text, 128, 1) else '' end
+ case when substring(mytable.text, 129, 1) not like '[0-9]' then substring(mytable.text, 129, 1) else '' end
+ case when substring(mytable.text, 130, 1) not like '[0-9]' then substring(mytable.text, 130, 1) else '' end
+ case when substring(mytable.text, 131, 1) not like '[0-9]' then substring(mytable.text, 131, 1) else '' end
+ case when substring(mytable.text, 132, 1) not like '[0-9]' then substring(mytable.text, 132, 1) else '' end
+ case when substring(mytable.text, 133, 1) not like '[0-9]' then substring(mytable.text, 133, 1) else '' end
+ case when substring(mytable.text, 134, 1) not like '[0-9]' then substring(mytable.text, 134, 1) else '' end
+ case when substring(mytable.text, 135, 1) not like '[0-9]' then substring(mytable.text, 135, 1) else '' end
+ case when substring(mytable.text, 136, 1) not like '[0-9]' then substring(mytable.text, 136, 1) else '' end
+ case when substring(mytable.text, 137, 1) not like '[0-9]' then substring(mytable.text, 137, 1) else '' end
+ case when substring(mytable.text, 138, 1) not like '[0-9]' then substring(mytable.text, 138, 1) else '' end
+ case when substring(mytable.text, 139, 1) not like '[0-9]' then substring(mytable.text, 139, 1) else '' end
+ case when substring(mytable.text, 140, 1) not like '[0-9]' then substring(mytable.text, 140, 1) else '' end
+ case when substring(mytable.text, 141, 1) not like '[0-9]' then substring(mytable.text, 141, 1) else '' end
+ case when substring(mytable.text, 142, 1) not like '[0-9]' then substring(mytable.text, 142, 1) else '' end
+ case when substring(mytable.text, 143, 1) not like '[0-9]' then substring(mytable.text, 143, 1) else '' end
+ case when substring(mytable.text, 144, 1) not like '[0-9]' then substring(mytable.text, 144, 1) else '' end
+ case when substring(mytable.text, 145, 1) not like '[0-9]' then substring(mytable.text, 145, 1) else '' end
+ case when substring(mytable.text, 146, 1) not like '[0-9]' then substring(mytable.text, 146, 1) else '' end
+ case when substring(mytable.text, 147, 1) not like '[0-9]' then substring(mytable.text, 147, 1) else '' end
+ case when substring(mytable.text, 148, 1) not like '[0-9]' then substring(mytable.text, 148, 1) else '' end
+ case when substring(mytable.text, 149, 1) not like '[0-9]' then substring(mytable.text, 149, 1) else '' end
+ case when substring(mytable.text, 150, 1) not like '[0-9]' then substring(mytable.text, 150, 1) else '' end
+ case when substring(mytable.text, 151, 1) not like '[0-9]' then substring(mytable.text, 151, 1) else '' end
+ case when substring(mytable.text, 152, 1) not like '[0-9]' then substring(mytable.text, 152, 1) else '' end
+ case when substring(mytable.text, 153, 1) not like '[0-9]' then substring(mytable.text, 153, 1) else '' end
+ case when substring(mytable.text, 154, 1) not like '[0-9]' then substring(mytable.text, 154, 1) else '' end
+ case when substring(mytable.text, 155, 1) not like '[0-9]' then substring(mytable.text, 155, 1) else '' end
+ case when substring(mytable.text, 156, 1) not like '[0-9]' then substring(mytable.text, 156, 1) else '' end
+ case when substring(mytable.text, 157, 1) not like '[0-9]' then substring(mytable.text, 157, 1) else '' end
+ case when substring(mytable.text, 158, 1) not like '[0-9]' then substring(mytable.text, 158, 1) else '' end
+ case when substring(mytable.text, 159, 1) not like '[0-9]' then substring(mytable.text, 159, 1) else '' end
+ case when substring(mytable.text, 160, 1) not like '[0-9]' then substring(mytable.text, 160, 1) else '' end
+ case when substring(mytable.text, 161, 1) not like '[0-9]' then substring(mytable.text, 161, 1) else '' end
+ case when substring(mytable.text, 162, 1) not like '[0-9]' then substring(mytable.text, 162, 1) else '' end
+ case when substring(mytable.text, 163, 1) not like '[0-9]' then substring(mytable.text, 163, 1) else '' end
+ case when substring(mytable.text, 164, 1) not like '[0-9]' then substring(mytable.text, 164, 1) else '' end
+ case when substring(mytable.text, 165, 1) not like '[0-9]' then substring(mytable.text, 165, 1) else '' end
+ case when substring(mytable.text, 166, 1) not like '[0-9]' then substring(mytable.text, 166, 1) else '' end
+ case when substring(mytable.text, 167, 1) not like '[0-9]' then substring(mytable.text, 167, 1) else '' end
+ case when substring(mytable.text, 168, 1) not like '[0-9]' then substring(mytable.text, 168, 1) else '' end
+ case when substring(mytable.text, 169, 1) not like '[0-9]' then substring(mytable.text, 169, 1) else '' end
+ case when substring(mytable.text, 170, 1) not like '[0-9]' then substring(mytable.text, 170, 1) else '' end
+ case when substring(mytable.text, 171, 1) not like '[0-9]' then substring(mytable.text, 171, 1) else '' end
+ case when substring(mytable.text, 172, 1) not like '[0-9]' then substring(mytable.text, 172, 1) else '' end
+ case when substring(mytable.text, 173, 1) not like '[0-9]' then substring(mytable.text, 173, 1) else '' end
+ case when substring(mytable.text, 174, 1) not like '[0-9]' then substring(mytable.text, 174, 1) else '' end
+ case when substring(mytable.text, 175, 1) not like '[0-9]' then substring(mytable.text, 175, 1) else '' end
+ case when substring(mytable.text, 176, 1) not like '[0-9]' then substring(mytable.text, 176, 1) else '' end
+ case when substring(mytable.text, 177, 1) not like '[0-9]' then substring(mytable.text, 177, 1) else '' end
+ case when substring(mytable.text, 178, 1) not like '[0-9]' then substring(mytable.text, 178, 1) else '' end
+ case when substring(mytable.text, 179, 1) not like '[0-9]' then substring(mytable.text, 179, 1) else '' end
+ case when substring(mytable.text, 180, 1) not like '[0-9]' then substring(mytable.text, 180, 1) else '' end
+ case when substring(mytable.text, 181, 1) not like '[0-9]' then substring(mytable.text, 181, 1) else '' end
+ case when substring(mytable.text, 182, 1) not like '[0-9]' then substring(mytable.text, 182, 1) else '' end
+ case when substring(mytable.text, 183, 1) not like '[0-9]' then substring(mytable.text, 183, 1) else '' end
+ case when substring(mytable.text, 184, 1) not like '[0-9]' then substring(mytable.text, 184, 1) else '' end
+ case when substring(mytable.text, 185, 1) not like '[0-9]' then substring(mytable.text, 185, 1) else '' end
+ case when substring(mytable.text, 186, 1) not like '[0-9]' then substring(mytable.text, 186, 1) else '' end
+ case when substring(mytable.text, 187, 1) not like '[0-9]' then substring(mytable.text, 187, 1) else '' end
+ case when substring(mytable.text, 188, 1) not like '[0-9]' then substring(mytable.text, 188, 1) else '' end
+ case when substring(mytable.text, 189, 1) not like '[0-9]' then substring(mytable.text, 189, 1) else '' end
+ case when substring(mytable.text, 190, 1) not like '[0-9]' then substring(mytable.text, 190, 1) else '' end
+ case when substring(mytable.text, 191, 1) not like '[0-9]' then substring(mytable.text, 191, 1) else '' end
+ case when substring(mytable.text, 192, 1) not like '[0-9]' then substring(mytable.text, 192, 1) else '' end
+ case when substring(mytable.text, 193, 1) not like '[0-9]' then substring(mytable.text, 193, 1) else '' end
+ case when substring(mytable.text, 194, 1) not like '[0-9]' then substring(mytable.text, 194, 1) else '' end
+ case when substring(mytable.text, 195, 1) not like '[0-9]' then substring(mytable.text, 195, 1) else '' end
+ case when substring(mytable.text, 196, 1) not like '[0-9]' then substring(mytable.text, 196, 1) else '' end
+ case when substring(mytable.text, 197, 1) not like '[0-9]' then substring(mytable.text, 197, 1) else '' end
+ case when substring(mytable.text, 198, 1) not like '[0-9]' then substring(mytable.text, 198, 1) else '' end
+ case when substring(mytable.text, 199, 1) not like '[0-9]' then substring(mytable.text, 199, 1) else '' end
+ case when substring(mytable.text, 200, 1) not like '[0-9]' then substring(mytable.text, 200, 1) else '' end as chars
from sys.messages mytable
A significantly less efficient but length-independent method is below. I don't suggest running this against 50,000 rows as in the above example.
declare @table table(
id int identity(1,1)
, string nvarchar(500)
, chars nvarchar(500) default ''
, ints nvarchar(500) default ''
)
insert into @table(string)
select 'ABC3434DEF5454'
insert into @table(string)
select 'dfbvhm,nk,k43543242455675645645654654'
insert into @table(string)
select 'xxxx,nk,k43543242455675645645654654'
insert into @table(string)
select 'xxxx,nk,k43543242455675645645654654'
insert into @table(string)
select 'xxxx,nk,k43543242455675645645654654'
insert into @table(string)
select 'xxxx,nk,56565656'
insert into @table(string)
select '145454545xxxx,nk,56565656'
declare @tableIter int = 1;
declare @rowCount int = (select max(id) from @table)
while @tableIter <= @rowCount
begin
declare @string nvarchar(max) = (select string from @table temptable where temptable.id = @tableIter)
declare @length int = len(@string)
declare @stringIter int = 1
while @stringIter <= @length
begin
if SUBSTRING(@string, @stringIter, 1) not like '[0-9]'
begin
update@table
setchars = chars + SUBSTRING(@string, @stringIter, 1)
where id = @tableIter
end
else
begin
update@table
setints = ints + SUBSTRING(@string, @stringIter, 1)
where id = @tableIter
end
set @stringIter = @stringIter + 1
end
set @tableIter = @tableIter + 1
end
select id, string, chars, ints
from @table
I can't say either of these approaches appeals to me. Hopefully someone will be able to produce a more efficient method, but above should give you something to start with.
October 20, 2016 at 8:47 pm
Another approach would be to create an ITVF for numbers and one for letters. Then you'll be able to use the functions to isolate the characters of each type. You'll also be able to use them for other things as well. If this is something you do frequently, consider creating a utility database and grant select permissions on the functions to public. Then you'll be able to use them from any database you want.
Here's a function to return a cleaned string consisting of only numbers. It uses a tally table to generate a table of numbers to cover the length of the string. If you'd like to read more about tally tables, Jeff's excellent article is at http://www.sqlservercentral.com/articles/T-SQL/62867/.
ALTER FUNCTION dbo.CleanNumbers(@OriginalText Varchar(8000)) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
Tally(N) AS (
SELECT TOP (ISNULL(DATALENGTH(@OriginalText), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteCleaned(CleanText) AS (
SELECT (SELECT SUBSTRING(@OriginalText, t.N, 1)
FROM Tally t
WHERE ASCII(SUBSTRING(@OriginalText, t.N, 1)) BETWEEN 48 AND 57
FOR XML PATH(''))
)
SELECT CleanText
FROM cteCleaned;
Once you have the function created, you can extract the numbers from it like this:
WITH cteData AS (
SELECT x.String
FROM (VALUES('XDX114A77400'),
('1123AB00171')
) x (String)
)
SELECT d.String, Numbers = cn.CleanText
FROM cteData d
CROSS APPLY dbo.CleanNumbers(d.String) cn
ORDER BY d.String;
For the alphabetic characters, you'd need to create a separate function and change the ASCII values of the permitted characters. Then just call it the same way and you're done.
Hope this helps.
October 21, 2016 at 2:53 am
You can use Alan Burstein's PatExclude8K[/url]
WITH cteData AS (
SELECT x.String
FROM (VALUES('XDX114A77400'),
('1123AB00171')
) x (String)
)
SELECT d.String
, Letters = L.NewString
, Numbers = N.NewString
FROM cteData d
CROSS APPLY dbo.PatExclude8K(d.String,'%[^A-Za-z]%') L
CROSS APPLY dbo.PatExclude8K(d.String,'%[^0-9]%') N
ORDER BY d.String;
October 21, 2016 at 2:15 pm
Thank you so much for all of the help. It's really marvellous. I used Alan Burstein's PatExclude8K, and it did work perfectly for me.
Regards,
Minh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply