从字符串中除去特殊字符和字母，但db2中的sql查询中的数字除外 [英] Remove special characters and alphabets from a string except number in sql query in db2

问题描述

您好，我尝试使用Regex_replace，但仍然无法正常工作.

Hi I tried using Regex_replace and it is still not working.

在sbbb<>中选择情况.''然后regexp_replace(sbbb，'[[a-zA-Z _-#]'，'']艾尔·塞伯(ELSE sbbb)AS ABCDF从表中sccc = 1;

select CASE WHEN sbbb <> ' ' THEN regexp_replace(sbbb,'[a-zA-Z _-#]',''] ELSE sbbb AS ABCDF from Table where sccc=1;

这是我要从字符串中删除字母和特殊字符且仅包含数字的查询.但它不起作用.查询返回完整的字符串，包括数字，字符和特殊字符.上面的查询有什么问题

This is the query which I am using to remove alphabets and specials characters from string and have only numbers. but it doesnot work. Query returns me the complete string with numbers,characters and special characters .What is wrong in the above query

我正在处理SQL查询.数据库中有一列，包含字符，特殊字符和数字.我只想保留数字并删除所有特殊字符和字母.如何在DB2查询中做到这一点.如果使用PATINDEX，它将无法正常工作.请在这里帮助.

I am working on a sql query. There is a column in database which contains characters,special characters and numbers. I want to only keep the numbers and remove all the special characters and alphabets. How can I do it in query of DB2. If a use PATINDEX it is not working. please help here.

推荐答案

此页面列出了允许的正则表达式模式

The allowed regular expression patterns are listed on this page

>正则表达式控制字符

在集合之外，必须在后面加上反斜杠以将其视为原义文字

Outside of a set, the following must be preceded with a backslash to be treated as a literal

*吗?+ [(){} ^ $ |\./

在集合中，必须在跟随符之前加反斜杠以将其视为原义文字

Inside a set, the follow must be preceded with a backslash to be treated as a literal

必须被引用为文字的字符为 [] \ 根据上下文，可能需要加引号的字符是-&

Characters that must be quoted to be treated as literals are [ ] \ Characters that might need to be quoted, depending on the context are - &

所以对您来说，这应该起作用

So for you, this should work

regexp_replace(sbbb,'[a-zA-Z _\-#]','')

这篇关于从字符串中除去特殊字符和字母，但db2中的sql查询中的数字除外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！