CODEWITHSUNDEEP
sqlsearchnumbersteradata
Frank
Frank

Reputation: 1

Teradata SQL Find numbers in a Character field

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"

Upvotes: 0

Views: 1037

Answers (3)

Adish G Krishna
Adish G Krishna

Reputation: 5

SELECT name 
FROM <table>
WHERE name LIKE ANY ('%0%','%1%','%2%','%3%','%4%','%5%','%6%','%7%','%8%','%9%');

Upvotes: 0

tinazmu
tinazmu

Reputation: 5139

An option would be:

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

Upvotes: 0

Andrew
Andrew

Reputation: 8693

You can use REGEXP_INSTR :

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

Upvotes: 1

Related Questions