syntax help on brining only numeric values data .

Database
Highlighted
Enthusiast

syntax help on brining only numeric values data .

Hi All,

How to get only numeric column values? I have data starts with

0000342842995    BANK OF THE PHIL /B
0000178583873    EATON VANCE CORP /B
0000028472358    KOTAK MAHINDRA B /B
0000242669600    CYNTHIA L THOMA  /B
0000272510649    RYAN NAKAMURA    /B 

 

want to truncate or eleminate  numeric values from above data. so it should be

 

EATON VANCE CORP /B
  KOTAK MAHINDRA B /B
  CYNTHIA L THOMA /B
  RYAN NAKAMURA   /B

 

Here goes the statement i wrote 

 (case when  (Var like'FED#%' OR  Var like'SEQ%') THEN  trim(substr(var, POSITION(' ' IN var))) ELSE var END ) AS test , 

its triminng the  the values starting with FED and SEQ .same i want to trim numbers also.

 

SEQ184037 BODY SCULPT INTL, LLC   /B                                        BODY SCULPT INTL, LLC   /B
FED#06306 BANK OF AMERICA, N /FTR/BN                                    BANK OF AMERICA, N /FTR/BN
FED#06267 PNC BANK, NATIONAL /FTR/BN                                    PNC BANK, NATIONAL /FTR/BN
SEQ186508 BRIGHAM CITY PROPERTIES /B                                  BRIGHAM CITY PROPERTIES /B
0000272510649    RYAN NAKAMURA    /B                                        0000272510649             RYAN NAKAMURA    /B            
FED#06306 BANK OF AMERICA, N /FTR/BN                                    BANK OF AMERICA, N /FTR/BN

 

I am trying to use      (case when   Var like '[0-9]%'  THEN  trim(substr(var, POSITION(' ' IN var))) ELSE var END )  as test  ,    -This is not working for numeric values .

 

 

here goes my variable column data:

0000342842995    BANK OF THE PHIL /B
0000178583873    EATON VANCE CORP /B
0000028472358    KOTAK MAHINDRA B /B
0000242669600    CYNTHIA L THOMA  /B
FED#06267 PNC BANK, NATIONAL /FTR/BN
SEQ184037 BODY SCULPT INTL, LLC   /B
FED#06306 BANK OF AMERICA, N /FTR/BN
FED#06267 PNC BANK, NATIONAL /FTR/BN
SEQ186508 BRIGHAM CITY PROPERTIES /B
0000272510649    RYAN NAKAMURA    /B    - i want to trim this value. i dont want to get numeric numbers.
FED#06306 BANK OF AMERICA, N /FTR/BN

 

Please any ideas .

 

Thanks,

Sundeep


Accepted Solutions
Teradata Employee

Re: syntax help on brining only numeric values data .

I'm looking for words or #, followed by a digit, followed by words.

Then I'm replacing them with nothing.

 

I'm using the following cheat sheet for reminders around regular expression - not fluent with them :

https://www.cheatography.com/davechild/cheat-sheets/regular-expressions

1 ACCEPTED SOLUTION
11 REPLIES
Senior Apprentice

Re: syntax help on brining only numeric values data .

Hi,

 

I think you need to use REGEXP_REPLACE. try using the following:

REGEXP_REPLACE(Var,'[0-9]','',1,0,'i')

HTH,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: syntax help on brining only numeric values data .

Hi skchintha,

 

The regexp (\w|#)*\d\w* seems to do the trick :

create multiset volatile table mvt_string, no log
( pi_mvt byteint
, str varchar(50)
)
primary index (pi_mvt)
on commit preserve rows; insert into mvt_string (pi_mvt, str) values (3, '0000342842995 BANK OF THE PHIL /B'); insert into mvt_string (pi_mvt, str) values (3, '0000178583873 EATON VANCE CORP /B'); insert into mvt_string (pi_mvt, str) values (3, '0000028472358 KOTAK MAHINDRA B /B'); insert into mvt_string (pi_mvt, str) values (3, '0000242669600 CYNTHIA L THOMA /B'); insert into mvt_string (pi_mvt, str) values (3, 'FED#06267 PNC BANK, NATIONAL /FTR/BN'); insert into mvt_string (pi_mvt, str) values (3, 'SEQ184037 BODY SCULPT INTL, LLC /B'); insert into mvt_string (pi_mvt, str) values (3, 'FED#06306 BANK OF AMERICA, N /FTR/BN'); insert into mvt_string (pi_mvt, str) values (3, 'FED#06267 PNC BANK, NATIONAL /FTR/BN'); insert into mvt_string (pi_mvt, str) values (3, 'SEQ186508 BRIGHAM CITY PROPERTIES /B'); insert into mvt_string (pi_mvt, str) values (3, '0000272510649 RYAN NAKAMURA /B'); insert into mvt_string (pi_mvt, str) values (3, 'FED#06306 BANK OF AMERICA, N /FTR/BN'); select str
, regexp_replace(str, '(\w|#)*\d\w*', '') as str_nodigit from mvt_string where pi_mvt = 3;

 

STR                                   STR_NODIGIT
------------------------------------  ---------------------------
0000342842995    BANK OF THE PHIL /B      BANK OF THE PHIL /B
0000178583873    EATON VANCE CORP /B      EATON VANCE CORP /B
0000028472358    KOTAK MAHINDRA B /B      KOTAK MAHINDRA B /B
0000242669600    CYNTHIA L THOMA  /B      CYNTHIA L THOMA  /B
FED#06267 PNC BANK, NATIONAL /FTR/BN   PNC BANK, NATIONAL /FTR/BN
SEQ184037 BODY SCULPT INTL, LLC   /B   BODY SCULPT INTL, LLC   /B
FED#06306 BANK OF AMERICA, N /FTR/BN   BANK OF AMERICA, N /FTR/BN
FED#06267 PNC BANK, NATIONAL /FTR/BN   PNC BANK, NATIONAL /FTR/BN
SEQ186508 BRIGHAM CITY PROPERTIES /B   BRIGHAM CITY PROPERTIES /B
0000272510649    RYAN NAKAMURA    /B      RYAN NAKAMURA    /B
FED#06306 BANK OF AMERICA, N /FTR/BN   BANK OF AMERICA, N /FTR/BN

 

Enthusiast

Re: syntax help on brining only numeric values data .

Hi ,

 

Thanks for your answer.

 

can u explain how it works

this expression :

 

regexp (\w|#)*\d\w*  )

 

thanks

Teradata Employee

Re: syntax help on brining only numeric values data .

I'm looking for words or #, followed by a digit, followed by words.

Then I'm replacing them with nothing.

 

I'm using the following cheat sheet for reminders around regular expression - not fluent with them :

https://www.cheatography.com/davechild/cheat-sheets/regular-expressions

Enthusiast

Re: syntax help on brining only numeric values data .

I am using ur code in my sql here goes results  "

    select TRAN_STMT_DESC_1     , regexp_replace(TRAN_STMT_DESC_1, '(\w|#)*\d\w*', '') as str_nodigit
   from  Merch_3_ACH_FACT where  tRAN_STMT_DESC_1 like 'WT%'; 

 how to eliminate  WT from it

WT FED#08782 CITIBANK N.A. NEW  /FTR/BNF                        WT  CITIBANK N.A. NEW  /FTR/BNF  - eliminate 'WT' from  it.
WT FED#01660 CITIBANK, N.A.     /FTR/BNF                             WT  CITIBANK, N.A.     /FTR/BNF
WT FED#02833 BMO HARRIS BANK NA /FTR/BNF                       WT  BMO HARRIS BANK NA /FTR/BNF

Enthusiast

Re: syntax help on brining only numeric values data .

actually i can explain how my data looks here in table :

 

WT 0001269349169    CHICAGO
WT FED#00592 M AND T BANK  
WT FED#00967 PRIVATBANK,   
WT FED#00976 JPMORGAN CHASE
WT SEQ175458 WELLS FARGO EQU
WT SEQ188558 NATIONSTAR MORT

WT 180831-133075 ZAGREBACKA
WT 180831-158921 CORPORATION
WT 180831-171728 BANK OF CHI

WT 180831-BANK OF america

 

Need do eliminate the first all junk characters and bring only names

CHICAGO
ZAGREBACKA

CORPORATION

 BANK OF CHI
M AND T BANK
PRIVATBANK,
JPMORGAN CHASE
WELLS FARGO EQU
NATIONSTAR MORT

 

my syntax:

 

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 3789 StartFragment: 314 EndFragment: 3757 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

(case when TRAN_STMT_DESC_1 like'WT%' 
   THEN     trim(substr(TRAN_STMT_DESC_1, POSITION(' ' IN TRAN_STMT_DESC_1))) ELSE TRAN_STMT_DESC_1 END )AS  VAR1  , (case when  (VAR1 like'FED#%' OR  VAR1 like'SEQ%'  ) THEN     trim(substr(VAR1, POSITION(' ' IN VAR1))) ELSE VAR1 END ) AS TRAN_STMT_DESC_1

divided into 2 cases statments. trying to implement best way to do it in one statement .

 

 

 

Teradata Employee

Re: syntax help on brining only numeric values data .

Ok, I've changed the method.

 

I do it in two steps, maybe it's possible to do a single step.

It may use a bit CPU, but I don't think it would be a performance issue anyway.

 

create multiset volatile table mvt_string, no log
( pi_mvt byteint
, str varchar(50)
)
primary index (pi_mvt)
on commit preserve rows;

insert into mvt_string (pi_mvt, str) values (3, 'WT 0001269349169    CHICAGO ');
insert into mvt_string (pi_mvt, str) values (3, 'WT FED#00592 M AND T BANK   ');
insert into mvt_string (pi_mvt, str) values (3, 'WT FED#00967 PRIVATBANK,    ');
insert into mvt_string (pi_mvt, str) values (3, 'WT FED#00976 JPMORGAN CHASE ');
insert into mvt_string (pi_mvt, str) values (3, 'WT SEQ175458 WELLS FARGO EQU');
insert into mvt_string (pi_mvt, str) values (3, 'WT SEQ188558 NATIONSTAR MORT');
insert into mvt_string (pi_mvt, str) values (3, 'WT 180831-133075 ZAGREBACKA ');
insert into mvt_string (pi_mvt, str) values (3, 'WT 180831-158921 CORPORATION');
insert into mvt_string (pi_mvt, str) values (3, 'WT 180831-171728 BANK OF CHI');
insert into mvt_string (pi_mvt, str) values (3, 'WT 180831-BANK OF america   ');

select str
     , regexp_substr(str, '(\D+)$')                                        as no_digit_end_str  -- Get any non digit characters before the end of the string
     , regexp_replace(no_digit_end_str, '^([[:punct:]]|[[:blank:]])+', '') as str_cleaned       -- Remove leading blank, tab, space or punctuation characters
  from mvt_string
 where pi_mvt = 3;

str                           no_digit_end_str  str_cleaned
---------------------------   ----------------  ----------------
WT 0001269349169    CHICAGO       CHICAGO       CHICAGO
WT FED#00592 M AND T BANK      M AND T BANK     M AND T BANK
WT FED#00967 PRIVATBANK,       PRIVATBANK,      PRIVATBANK,
WT FED#00976 JPMORGAN CHASE    JPMORGAN CHASE   JPMORGAN CHASE
WT SEQ175458 WELLS FARGO EQU   WELLS FARGO EQU  WELLS FARGO EQU
WT SEQ188558 NATIONSTAR MORT   NATIONSTAR MORT  NATIONSTAR MORT
WT 180831-133075 ZAGREBACKA    ZAGREBACKA       ZAGREBACKA 
WT 180831-158921 CORPORATION   CORPORATION      CORPORATION
WT 180831-171728 BANK OF CHI   BANK OF CHI      BANK OF CHI
WT 180831-BANK OF america     -BANK OF america  BANK OF america
Enthusiast

Re: syntax help on brining only numeric values data .

Thanks for your solution .

unforutunaltey i am getting no more spool space . due to huge data .

 

 

Junior Contributor

Re: syntax help on brining only numeric values data .

This combines @Waldars regex into a single RegExp_Replace:

RegExp_Replace(str,'.*?\d+([[:punct:][:blank:]])*')

Remove any characters before some digits and optionally following spaces, commas, etc.