Teradata SQL Find numbers in a Character field-CodePudding

I have a table with a character column called "Name". In some cases the "Name" columns contains string with just characters. In other cases it contains any of the 10 numeric digits (i.e. 0 - 9). I want to query this table and only produce the records where the Name column contains any numbers (0 -9) regardless of what order the numbers are in or in what position they are in. Below is a hypothetical example: The table contains the following 5 records of data in the Name column: "ABC Company" , "Terry Smith" , "Trucking 365 LLC" , "Lumber3foryou" , "24/7 Carwash"

I want to create a query that will produce the Name column with only those records that have any number (0 - 9), regardless of what order the numbers are in or in what positions they are in.

So the output should include only the last three records:

"Trucking 365 LLC" , "Lumber3foryou" , "24/7 Carwash"

CodePudding user response：

You can use REGEXP_INSTR :

select
name
from
<table>
where
REGEXP_INSTR (name,'[0-9]') > 0

CodePudding user response：

An option would be:

name like any ('%0%','%1%','%2%','%3%','%4%',
               '%5%','%6%','%7%','%8%','%9%')