Retrieving records of strings and numbers

Analytics
Highlighted
Enthusiast

Retrieving records of strings and numbers

Hi There,

 

I currently have various records of letters and numbers in a VARCHAR format. They do have a "code" to them, but are laid out in different variations (see below)

I was wondering if there is a simpler way to retrieve only records of a specific pattern.

 

EG:

tablename EXAMPLE

columnname RECORD

  1. JOHN;2018MAR06
  2. DEBRA=2018--JAN--20
  3. ABBIE: 2018-MAR-14
  4. JANE//18/MAR/03
  5. MARK: 2018/ JUN- 02
  6. KEVIN@18//MAR//03
  7. MARTIN(2018 MAR 01)
  8. MARGE:18JUN02
  9. LEO^2018=MAR=07
  10. 18;JAN;20

 

In this example, I want to pull only records where it is (strictly) "MAR" then followed by ANY number regardless of the variation (delimiter) it is in.

LIKE statements won't work here since "MAR" appears in certain names.

 

NOTE: Example above is a mock using names and dates of births.

My original dataset has been transposed to its requirements already and cannot be transposed further (therefore, we cannot simply split the name and then use LIKE statements; it must be under one line).

 

Thank you in advance


Accepted Solutions
Teradata Employee

Re: Retrieving records of strings and numbers

Seems like a job for REGEXP functions. For example,

WHERE REGEXP_INSTR(col,'[^A-Z].*MAR.*[0-9]') > 0

returns rows where something other than an uppercase letter A-Z is followed by zero or more characters of any kind, the three letters MAR, zero or more additional characters, and then a digit.

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Retrieving records of strings and numbers

Seems like a job for REGEXP functions. For example,

WHERE REGEXP_INSTR(col,'[^A-Z].*MAR.*[0-9]') > 0

returns rows where something other than an uppercase letter A-Z is followed by zero or more characters of any kind, the three letters MAR, zero or more additional characters, and then a digit.

Enthusiast

Re: Retrieving records of strings and numbers

Thanks Fred :)