仅当字符串以数字值开头时才选择字符串的数字部分
这只是给我最后一个字符（数字），但我需要整个数字字符串
SELECT substring('123 Main Street' FROM '%#"[0-9]#"%' FOR '#')
- 结果：3
- 预期：123
这给了我相同的结果，但我需要它返回一个空白值：
SELECT substring('Main 123 Street' FROM '%#"[0-9]#"%' FOR '#')
- 结果：3
- 预期：
注意：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'
更新2：
几乎拥有它：
如果它不以数值开头，这会给我想要的结果：
SELECT
COALESCE(substring('Main 123 Street' FROM '[0-9]*') || 'Main 123 Street', ''),
substring('Main 123 Street' FROM '[0-9]*')
但这给了我两个，我只想要第二个条件：
SELECT
COALESCE(substring('123 Main Street' FROM '[0-9]*') || '123 Main Street', ''),
substring('123 Main Street' FROM '[0-9]*')
我明白了！感谢所有发帖的人：
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
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 '#')
- Results: 3
- Expecting: 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 '#')
- Results: 3
- Expecting:
NOTE: Postgres 7.4
Helpful Link: http://www.postgresql.org/docs/7.4/static/functions-matching.html
UPDATE:
SELECT substring('Main 123 Street' FROM '[0-9]+')
SELECT substring('123 Main Street' FROM '[0-9]+')
- Both now return: 123
- Still need to skip or return full string of: 'Main 123 Street'
UPDATE 2:
Almost have it:
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我没有要测试的 Postgres 安装，但类似这样的东西可能会起作用：
SELECT substring('123 Main Street' FROM '^[0-9]+')如果它返回任何内容不以数字开头。如果您想返回完整字符串，则应该可以：
SELECT substring('123 Main Street' FROM '^[0-9]+|.*')I don't have a Postgres install to test with, but something like this might work:
SELECT substring('123 Main Street' FROM '^[0-9]+')That returns nothing if it doesn't start with a number. If you want to return the full string instead, this should work:
SELECT substring('123 Main Street' FROM '^[0-9]+|.*')也许现在改进 rmh 的答案还为时不晚。
这对我有用：
在 postgresql 中，只需删除要显示的数字的插入符号即可。
如果
column_name = 'XXXX-0001'结果将是：Maybe it is not yet too late to improve on rmmh's answer.
This works for me:
In postgresql, just remove the caret for the numbers to show.
if
column_name = 'XXXX-0001'the result will be:我不知道 postgresql 正则表达式语法，但在大多数正则表达式中你会写
[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.