如何从 SQL 结果中提取数值数据 [英] How to extract numerical data from SQL result

问题描述

假设有一个表A"有 2 列 - ID (INT)、DATA (VARCHAR(100)).执行SELECT DATA FROM A"的结果如下:

Suppose there is a table "A" with 2 columns - ID (INT), DATA (VARCHAR(100)). Executing "SELECT DATA FROM A" results in a table looks like:

DATA
---------------------
Nowshak 7,485 m
Maja e Korabit (Golem Korab) 2,764 m
Tahat 3,003 m
Morro de Moco 2,620 m
Cerro Aconcagua 6,960 m (located in the northwestern corner of the province of Mendoza)
Mount Kosciuszko 2,229 m
Grossglockner 3,798 m
// the DATA continues...
---------------------

如何在 SELECT SQL 查询中使用某种字符串处理函数仅提取数字数据，以便修改后的 SELECT 的结果如下所示:

How can I extract only the numerical data using some kind of string processing function in the SELECT SQL query so that the result from a modified SELECT would look like this:

DATA (in INTEGER - not varchar)
---------------------
7485
2764
3003
2620
6960
2229
3798
// the DATA in INTEGER continues...
---------------------

顺便说一句，如果这可以在单个 SQL 语句中完成，那将是最好的.(我使用的是 IBM DB2 9.5 版)

By the way, it would be best if this could be done in a single SQL statement. (I am using IBM DB2 version 9.5)

谢谢:)

推荐答案

我知道这个线程很旧，OP 不需要答案，但我不得不通过这个线程和其他线程的一些提示来解决这个问题.他们似乎都错过了确切的答案.

I know this thread is old, and the OP doesn't need the answer, but I had to figure this out with a few hints from this and other threads. They all seem to be missing the exact answer.

执行此操作的简单方法是将所有不需要的字符TRANSLATE 转换为单个字符，然后用空字符串REPLACE 那个单个字符.

The easy way to do this is to TRANSLATE all unneeded characters to a single character, then REPLACE that single character with an empty string.

DATA = 'Nowshak 7,485 m'

# removes all characters, leaving only numbers
REPLACE(TRANSLATE(TRIM(DATA), '_____________________________________________________________________________________________', ' abcdefghijklmnopqrstuvwzyaABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*()-_=+|[]{};:",.<>/?'), '_', '')
 => '7485'

<小时>

分解 TRANSLATE 命令:

TRANSLATE( FIELD or String, <to characters>, <from characters> )

例如

DATA = 'Sample by John'

TRANSLATE(DATA, 'XYZ', 'abc')
 => a becomes X, b becomes Y, c becomes Z
 => 'SXmple Yy John'

** 注意:我无法谈论性能或版本兼容性.我使用的是 9.x 版本的 DB2，并且是这项技术的新手.希望这对某人有所帮助.

** Note: I can't speak to performance or version compatibility. I'm on a 9.x version of DB2, and new to the technology. Hope this helps someone.

这篇关于如何从 SQL 结果中提取数值数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！