Like operator usage

Database
Enthusiast

Like operator usage

Hi All,

 I am from SQL server  background and new to Teradata.

i want to find the data  in a particular column where it has alphabets at anypalce and contains 6 characters like below.

I/P DATA:

ID:

123A45

34Z123

654321

3F123X

123456

 

O/P:

123A45

34Z123

3F123X

I wrote Query in SQL Server but the query is not working. Could some one help me.

below is the SQL query.

WHERE  (PATINDEX ( '%[A-Z]%', ID)>0 ) AND ID LIKE '______'

 

 

Thanks in advance!!.

 

 

 

Tags (1)

Accepted Solutions
Junior Contributor

Re: Like operator usage

Of course not, you got a CHAR(40) and there are 34 padding blanks:

'9999D9                                        '

which don't match the RegEx (and should not match LIKE '______' in Standard SQL).

 

So you can either apply TRIM

RegExp_Similar(trim(id), '(?=.*[A-Z])[A-Z0-9]{6}', 'i')

or add an arbitrary number of blanks to the RegEx

 RegExp_Similar(id, '(?=.*[A-Z])[A-Z0-9]{6} *', 'i')

Btw, it's quite a waste of space to store 6 characters in a CHAR(40), this should be fixed in the data model.

 

1 ACCEPTED SOLUTION
9 REPLIES
Teradata Employee

Re: Like operator usage

Teradata equivalent would be to use a regular expression function instead of SQL Server PATINDEX:

For example:

 

WHERE  (REGEXP_INSTR(ID, '[A-Z]')>0 ) AND ID LIKE '______'

Or if you need case-insensitive match include optional arguments:  REGEXP_INSTR(ID,'[A-Z]',1,1,1,'i')

 

Junior Contributor

Re: Like operator usage

This Regular Expression checks for exactly 6 alpha/digits, at least one alpha (case insensitive):

WHERE RegExp_Similar(id, '(?=.*[A-Z])[A-Z0-9]{6}', 'i') = 1

(?=.*[A-Z]) lookahead (without adding it to the overall match), checks if there's at least one alpha
[A-Z0-9]{6} checks for exactly 6 alpha/digits

 

Enthusiast

Re: Like operator usage

Thanks for the reply dnoeth,

 

below expression is not working. i have  only  one test id like '9999D6' and below expression is not returning.

Junior Contributor

Re: Like operator usage

What's your Teradata release, is it pre-15?

SELECT '9999D6'  AS id, RegExp_Similar(id, '(?=.*[A-Z])[A-Z0-9]{6}', 'i');

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 2 seconds.

id      RegExp_Similar(id,'(?=.*[A-Z])[A-Z0-9]{6}','i')
------  -----------------------------------------------
9999D6                                                1

 

 

Enthusiast

Re: Like operator usage

Hi Dnoeth,

I am using 15.10 teradata  SQL assistant.

i have attached original data queries. Please let me know what is wrong with my Query.

 

SyntaxEditor Code Snippet

SELECT   apo_prd_cd  FROM v1  WHERE  
RegExp_Similar(apo_prd_cd, '(?=.*[A-Z])[A-Z0-9]{6}', 'i')=1

1.jpg 

Junior Contributor

Re: Like operator usage

SQL Assistant 15.10 doesn't mean Teradata 15.10, which version is returned by SELECT * FROM dbc.DBCinfoV?

 

What's the data type of apo_prd_cd?

Does WHERE apo_prd_cd like '9999__' return a result, too?

Enthusiast

Re: Like operator usage

Hi Dnoeth,

 we are using  Teradata version15.10 04.03 ( ran the query given by you).

Datatype of apo_prd_Cd is :

CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

it is not returning any result when i ran WHERE apo_prd_cd like '9999__'.

 

Junior Contributor

Re: Like operator usage

Of course not, you got a CHAR(40) and there are 34 padding blanks:

'9999D9                                        '

which don't match the RegEx (and should not match LIKE '______' in Standard SQL).

 

So you can either apply TRIM

RegExp_Similar(trim(id), '(?=.*[A-Z])[A-Z0-9]{6}', 'i')

or add an arbitrary number of blanks to the RegEx

 RegExp_Similar(id, '(?=.*[A-Z])[A-Z0-9]{6} *', 'i')

Btw, it's quite a waste of space to store 6 characters in a CHAR(40), this should be fixed in the data model.

 

Enthusiast

Re: Like operator usage

 

Hi  dnoeth,

You are my saviour!!!. First expression worked perfectly (TRIM).

 

Thanks a lot for you patience and help!!!