I need to get all values from a table where in a column appears the text 'demo' followed a number.

My DB is Oracle.

ID    NAME
------------
1     demo20
2     demo26
3     demo
4     any

I just need the values ​​with the demo word with a number. At the moment I have this:

select * from table where NAME like 'demo';

CodePudding user response：

You can achieve this using REGEXP_LIKE

select * from t where REGEXP_LIKE(name, '^demo\d');

test here: https://sqlize.online/sql/oracle19/4284f904af9157489dc4a0c9d9515c7c/

 ==== ======== 
| ID | NAME   |
 ==== ======== 
| 1  | demo20 |
| 2  | demo26 |
 ---- -------- 

When you need filter strings not contains additional chars after digits you can use next:

select * from t where REGEXP_LIKE(name, '^demo\d $');

CodePudding user response：

You can find the names that start with the sub-string demo and have at least one following character using:

SELECT *
FROM   table_name
WHERE  name LIKE 'demo_%'

If you want to find demo followed by only trailing numbers then, from Oracle 12, you can use:

SELECT *
FROM   table_name
WHERE  name LIKE 'demo_%'
AND    VALIDATE_CONVERSION(SUBSTR(name, 5) AS NUMBER) = 1;

or, in most versions:

SELECT *
FROM   table_name
WHERE  REGEXP_LIKE(name, '^demo\d $');

Which, for the sample data:

CREATE TABLE table_name (name) AS
SELECT 'demo20' FROM DUAL UNION ALL
SELECT 'demo26' FROM DUAL UNION ALL
SELECT 'demo2a' FROM DUAL UNION ALL
SELECT 'demo'   FROM DUAL UNION ALL
SELECT 'any'    FROM DUAL;

Both output:

fiddle