Trimming specific fields

Analytics
Highlighted
Enthusiast

Trimming specific fields

Hi All,

 

I have a column with several fields having prefixes that I do not want. However, I don't want any 'perfect' fields to be trimmed.

Is there a way to trim specific fields?

 

Example

ColumnName

  1. A04123356
  2. A04235567
  3. B04356678
  4. 987654321843
  5. C04567789
  6. 876543217824
  7. 765432189015
  8. D04678890

 

 

I am currently using the following to remove the letters:

Select

REGEXP_SUBSTR(ColumnName, '[0-9]+') as NewColumnName

From DummyTable

 

I want to trim the letter and 04s from all the rows that have (letter)04, but keep the others (row 4,6,7) as they are.

How can it be done in one step, if possible?

 

 

The results should look like:

  1. 123356
  2. 235567
  3. 356678
  4. 987654321843
  5. 567789
  6. 876543217824
  7. 765432189015
  8. 678890

Accepted Solutions
Teradata Employee

Re: Trimming specific fields

Use regexp_replace:

select regexp_replace(ColumnName, '[A-Z]04', '');

 

select regexp_replace('A04123356 A04235567 B04356678 987654321843 C04567789 876543217824 765432189015 D04678890', '[A-Z]04', '')
returns:
123356 235567 356678 987654321843 567789 876543217824 765432189015 678890

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Trimming specific fields

Use regexp_replace:

select regexp_replace(ColumnName, '[A-Z]04', '');

 

select regexp_replace('A04123356 A04235567 B04356678 987654321843 C04567789 876543217824 765432189015 D04678890', '[A-Z]04', '')
returns:
123356 235567 356678 987654321843 567789 876543217824 765432189015 678890

Enthusiast

Re: Trimming specific fields

Thank you very much, GJColeman