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
Can someone please help me with the SQL query ?
Thankyou in advance
Solved! Go to Solution.
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.
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.