Wildcard characters

[scarletAni]

Hi !!
I have a table with a character column
Eg :
Junk_tbl
junk_id char(20)
junk_acct_no char(20)

I want to select only those rows which have all integer values in junk_acct_no.
i.e junk_acct_no will contain values such as
AA1234
123456
V56765
453533
I want the result to contain only
123456
453533
How do I make this wild card comparison ?

 

[RogerCruse]

Hi ScarletAni,
You probably need to use a CAST to convert the column into a number and then used this as a predicate to only return rows you interested in.

Roger...

 

[scarletAni]

When I use CAST to convert into an integer , I get the following error :
621: Bad character in format or data of <tablename>

I want to know if there is anything like a IsNumeric() or some wildcard pattern against which we can check.

 

[CVENOM]

This is a kind of cheesy way to do it, but it works...

Create a one column table that has just the letters in it from a to z.

Create table Charz
(Charz char(1));

insert into Charz values ('a');
insert into Charz values ('b');
....
insert into Charz values ('z');

Do your select against the Charz &quot;lookup table&quot;:

SEL junk_acct_no
FROM Junk_tbl
WHERE SUBSTRING(junk_acct_no FROM 1 FOR 1) NOT IN
(SELECT CHARZ
FROM CHARZ);

This will return what your looking for as long as all your data has the character data in the first position (Like the example you posted). This is just my quick answer, I'll keep looking to see if I can figure out a better way.

Rich

 

[RogerCruse]

What I was getting at was something like this -

select *
from tablestuff
where ((cast col1 as integer) / (cast col1 as integer))= 1;

Sorry, I'm at home at the moment, so can't give you the exact syntax or test it for you. If Teradata gives you a problem with the non-numeric values, then you may have to play around with the CAST statement and maybe do something like this -

select *
from tablestuff
where ((cast cal1 as integer)+1)=((cast cal1 as integer)+1);

But, hopefully, you can see that as the left hand side of the predicate can only equal the right hand side for numerica values then you've got the solution to your problem.

Roger...

 

[scarletAni]

CVenom ,
In your case, I have to do the same for each character in the column. ie. SUBSTR(col from 1 for 1), then SUBSTR(col from 2 for 1) etc for each char. This may be tedious.

Roger,
This CAST(col as integer) still gives me a BAD character or format error.

 

[RogerCruse]

ScarletAni,
As I said, I'm at home at the moment, so couldn't check the solution.

Roger...

 

[jgerstb]

Here is a solution. It assumes no leading or embedded spaces in your junk_acct_no value.If junk_acct_no will always fill all 20 characters with a value other than blank, remove ' ' from the in list. If junk_acct_no will be a consistent length shorter than 20 you can trim the value and remove ' ' from the in list and remove some of the AND conditions from the WHERE clause

SELECT junk_acct_no
FROM junk_tbl
WHERE substr(junk_acct_no),chars(junk_acct_no),1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -1,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -2,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -3,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -4,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -5,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -6,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -7,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -8,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -9,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -10,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -11,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -12,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -13,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -14,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -15,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -16,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -17,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -18,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no),chars(junk_acct_no) -19,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')

 

[jgerstb]

Sorry, I put an extra &quot&quot; in the substr.

SELECT junk_acct_no
FROM junk_tbl
WHERE substr(junk_acct_no,chars(junk_acct_no),1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -1,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -2,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -3,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -4,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -5,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -6,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -7,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -8,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -9,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -10,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -11,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -12,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -13,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -14,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -15,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -16,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -17,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -18,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')
AND substr(junk_acct_no,chars(junk_acct_no) -19,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', ' ')

 

[TheMartian]

Depending on the size of the table, this may or may not be a solution for you. As well as being more than a little cheesy it could be a bit inefficient on very large tables.

But try:

SELECT *
FROM MYSTUFF.TEKTIP
WHERE ACCT_NUM NOT LIKE ALL ('%A%', '%B%',
'%C%', '%D%', '%F%', '%H%');

or similar - including all alpha characters, uppercase and lower case, spaces etc for exclusion.

Alternatively to get all rows where the acct_num is not numeric:

SELECT *
FROM MYSTUFF.TEKTIP
WHERE ACCT_NUM LIKE ANY ('%A%', '%B%',
'%C%', '%D%', '%F%', '%H%');

Martin

 

[dnoeth]

jgerstb's solution rephrased, looks still ugly, but is quite efficient (i hope you have to do this only once in a stage table):

SELECT junk_acct_no
FROM junk_table
WHERE
position(substring(trim(junk_acct_no) from 1 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 2 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 3 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 4 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 5 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 6 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 7 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 8 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 9 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 10 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 11 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 12 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 13 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 14 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 15 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 16 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 17 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 18 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 19 for 1) in '0123456789') > 0
AND
position(substring(trim(junk_acct_no) from 20 for 1) in '0123456789') > 0


Dieter