如何从SQL结果提取数值数据 [英] How to extract numerical data from SQL result
问题描述
执行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屋!