Need help in using postion parameter or substring

Database

Need help in using postion parameter or substring

Hi All,

i have a field of char 100 bytes and i need to  find a text "BOX" in it. once i find the BOX in the field , i need to search for preceding character as O or O and space before BOX and also a number after BOX. Once valid value is found, i need to set a flag in the table to Y else to N.

Example of valid values are

 

PO BOX 123

PO BOX123

POBOX 123

POBOX123

Can someone please help me with the SQL query ?

Thankyou in advance


Accepted Solutions
Teradata Employee

Re: Need help in using postion parameter or substring

\d matches a digit (0-9).  \s matches a white-space character (such as space or tab).  . (period) matches any character.  A * after any of these matches zero, one or more of them.  Therefore, \s* matches zero, one, two, ... spaces.

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Need help in using postion parameter or substring

This is a job for regular expressions. For example, try

REGEXP_INSTR(theColumn,'O\s*BOX\s*\d',2,1,'i') > 0

Case insensitive match (i) , starting at column 2, look for first occurrence of letter O, followed by optional whitespace, followed by BOX, optional whitespace again, followed by a digit.

If found, returns the offset of the first matching character; if not found, returns zero.

Re: Need help in using postion parameter or substring

Thank you Fred for the quick solution. I assume /d searches for all values from 0-9 i.e for numeric field.Could you please confirm? I am asking this as the numeric field after BOX can be anything.
I assume \s* checks for single space after O. Could you please confirm the same?

I will test the below syntax once I go to office on Tuesday.Thank you
Teradata Employee

Re: Need help in using postion parameter or substring

Yes, \d is any digit. You could use a character class [0-9] instead, if you prefer.

The \s is "any white space character" - so includes space, non-breaking space, tab, etc. If you want only the space character itself, just use a space instead of \s.

The * means "0 or more of the preceding element". If you want "0 or 1" then use ? instead.

Teradata Employee

Re: Need help in using postion parameter or substring

\d matches a digit (0-9).  \s matches a white-space character (such as space or tab).  . (period) matches any character.  A * after any of these matches zero, one or more of them.  Therefore, \s* matches zero, one, two, ... spaces.

Re: Need help in using postion parameter or substring

Thank you all. The code worked and able to use it for my requirements. Thank you all