CODEWITHSUNDEEP
sqlregexteradata
user2020436
user2020436

Reputation: 29

teradata : filtering alpahanumeric & only alphabets

i have data in one of the columns which looks like

column data:

1.A123BDG

2.ADGHKKL

3.12HJKLL

4.78GHUBD

5.GHJKUIP

6.KGJHGJG

Now i have to filter out the data in such a way that i have detect which is alphanumeric and only alpahabets

select 

  case when <condition for detecting alphanumneric>
          then column_data
          end column1 ,
  case when <condition for detecting alphabets>
          then column_data
         end  column2

from source_table

data in column1 should be

1.A123BDG

3.12HJKLL

4.78GHUBD

data in column2 should be

2.ADGHKKL

5.GHJKUIP

6.KGJHGJG

anyone could help me by writing the the conditions for filtering alphanumeric & alphabets

Thanks in Advance

Upvotes: 0

Views: 8561

Answers (1)

TildalWave
TildalWave

Reputation: 1667

Regex_INSTR(Column,'[[:digit:]]') will return the position of the first digit in a character string so you could use it to determine, if your string (or a record field value passed as a parameter to the function) contains digits. Transferring this to your example, it would look like:

case when Regex_INSTR(Column,'[[:digit:]]') > 0
          then column_data
          end column1 ,
  case when Regex_INSTR(Column,'[[:digit:]]') <= 0
          then column_data
         end  column2

Note that it's not the best example you're giving, as there isn't any need to do double selections and you could simply use CASE WHEN <contains_numerals> THEN <do_something> ELSE <do_something_else> END block instead.

Upvotes: 2

Related Questions