仅当字符串以数字值开头时,才选择数字部分 [英] Select only numeric part of string only if it starts with a numeric value
问题描述
这只是给我最后一个字符(数字),但我需要整个数字字符串
This is only giving me the last character (numeric) but I need the whole numeric string
SELECT substring('123 Main Street' FROM '%#"[0-9]#"%' FOR '#')
- 结果:3
- 预期:123
我得到相同的结果,但我需要它返回一个空值:
This gives me the same results but I need it to return a blank value:
SELECT substring('Main 123 Street' FROM '%#"[0-9]#"%' FOR '#')
- 结果:3
- 期待中:
注意:Postgres 7.4
NOTE: Postgres 7.4
有用的链接: http:// www .postgresql.org / docs / 7.4 / static / functions-matching.html
更新:
SELECT substring('Main 123 Street' FROM '[0-9]+')
SELECT substring('123 Main Street' FROM '[0-9]+')
- 两个都返回:123
- 仍然需要跳过或返回以下字符串:'Main 123 Street'
UPDATE 2:
几乎拥有它:
如果没有,这会给我想要的结果以数值开头:
This gives me the results I want if it doesn't start with a numeric value:
SELECT
COALESCE(substring('Main 123 Street' FROM '[0-9]*') || 'Main 123 Street', ''),
substring('Main 123 Street' FROM '[0-9]*')
但这给了我两个人,我只想要第二个条件:
But this gives me both and I only want the second condition:
SELECT
COALESCE(substring('123 Main Street' FROM '[0-9]*') || '123 Main Street', ''),
substring('123 Main Street' FROM '[0-9]*')
我知道了!!!感谢所有发布者:
I GOT IT!!! Thanks for all who posted:
SELECT CASE
WHEN COALESCE(substring(db_column FROM '[0-9]*'), db_column) != '' THEN COALESCE(substring(db_column FROM '[0-9]*'), db_column)
ELSE db_column
END AS addsress_string
FROM db_table
推荐答案
我不知道PostgreSQL regex语法,但是在大多数regex中,您会写 [0-9] +
。如果没有量词, [0-9]
匹配单个字符。
I don't know postgresql regex syntax, but in most regex you would write [0-9]+
. Without the quantifier, [0-9]
matches a single character.
这篇关于仅当字符串以数字值开头时,才选择数字部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!