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

问题描述

假设有一个具有2列的表A - ID（INT），DATA（VARCHAR（100））。
执行SELECT DATA FROM A结果表格如下：

  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米（位于门多萨省西北角）
 Mount Kosciuszko 2,229 m 
 Grossglockner 3,798 m 
 //数据继续.. 
 --------------------- 
  

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

  DATA（INTEGER  -  not varchar）
 --------------------- 
 7485 
 2764 
 3003 
 2620 
 6960 
 2229 
 3798 
 // INTEGER中的数据继续... 
  - ------------------- 
  

由方式，最好是在单个SQL语句中完成。 （我正在使用IBM DB2版本9.5）

谢谢：）

解决方案

p>我知道这个线程是老的，OP不需要答案，但是我不得不从这个线程和其他线程中提取几个提示。他们似乎都缺少确切的答案。

这样做的简单方法是 TRANSLATE 所有不需要的字符单个字符，然后 REPLACE 该单个字符为空字符串。

  DATA ='Nowshak 7,485 m'
 
＃删除所有字符，只留下数字
 REPLACE（TRANSLATE（TRIM（DATA）），'abcdefghijklmnopqrstuvwzyaABCDEFGHIJKLMNOPQRSTUVWXYZ`〜！@＃$ ％^& *（）-_ = + \ | [] {} ;:，。<> /？'），'_'，''）
 =>'7485'
  

---

要分解 TRANSLATE 命令：

  TRANSLATE（FIELD或String，< to characters>< from characters> ）
  

例如

 code> DATA ='Sample by John'
 
 TRANSLATE（DATA，'XYZ'，'abc'）
 => a X，b变为Y，c变为Z 
 => 'SXmple Yy John'
  

**注意：我不能说性能或版本兼容性。我在一个9.x版本的DB2，而且是新的技术。希望这有助于某人。

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...
---------------------

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...
---------------------

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)

Thanks :)

解决方案

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.

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'

---

To break down the TRANSLATE command:

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

e.g.

DATA = 'Sample by John'

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

** 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屋！