Home » SQL & PL/SQL » SQL & PL/SQL » string function or utility help
string function or utility help [message #300268] Thu, 14 February 2008 16:44 Go to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Hi guys,

I have a alpha/numeric field with 9 characters.
I want to have following check condition for this field
while selecting from the table.


1. First letter should be either 'A' or 'B'.
2. Second letter should be between 'A' TO 'Z'.
3. last Three to Nine letter should be between 0 to 9.

I have tried something following, but it would be great
to have such utiliy or function which can check above conditions
and then select from.

Note: following statement is also giving incorrect output


SELECT CASE 
        WHEN SUBSTR('AB1234567',1,1) IN ('A','B') THEN
         CASE 
         WHEN SUBSTR('AB1234567',2,1) IN   
                ('A','B','C','D','E','F','G','H','I','J','K','L','M',
                 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z') THEN
          CASE
            WHEN SUBSTR('AB1234567',3,7) IN ('0','1','2','3','4','5','6','7','8','9')
            
             THEN 'AB1234567'  ---> Should print this  
             
            ELSE  'ABCD12356'  ----> but printing this 
            
          END
        END
       END CASE 
FROM DUAL;




Any help will be much appreciated.


Re: string function or utility help [message #300269 is a reply to message #300268] Thu, 14 February 2008 17:19 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Have a look at regular expressions

Im not at a terminal, but something like this would suffice.

select * from table
where regexp_like(column_name,'[AB][A-Z][0-9][0-9][0-9]');
Re: string function or utility help [message #300273 is a reply to message #300269] Thu, 14 February 2008 19:36 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member

Thanks for your reply.
but , Isn't it for 10g and above?
I have provided my oracle verison : 9.2.0.1.0
Re: string function or utility help [message #300296 is a reply to message #300269] Thu, 14 February 2008 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@coleing

Stop putting solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usually best to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, rather than providing complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


In addition read CAREFULLY what is posted. Version was posted by OP in its first post.

Regards
Michel

[Updated on: Thu, 14 February 2008 23:07]

Report message to a moderator

Re: string function or utility help [message #300299 is a reply to message #300268] Thu, 14 February 2008 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@ashwin_tampa

CASE conditions support AND, you don't need to nest CASEs.
Have a look at TRANSLATE and LENGTH for the third condition (clue: instead of checking if letters are good, check if there is wrong letter).

Regards
Michel
Re: string function or utility help [message #300476 is a reply to message #300299] Fri, 15 February 2008 09:57 Go to previous message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Michel Thanks a lot for pointing to the right direction.

Here is what I was able to follow.
And it is giving my expected results.
Please let me know if I am missing anything here.
Or if there is other way to get same results.



SELECT DISTINCT MY_FIELD
  FROM (SELECT 'AB0123456' AS MY_FIELD
        FROM DUAL
        WHERE   LENGTH ('AB0123456') - 
        LENGTH (TRANSLATE ('AB0123456',CHR (1)|| TRANSLATE ('AB0123456',CHR (1)|| '1234567890', CHR (1)), CHR (1))) > 0)
WHERE (SUBSTR ('AB0123456', 1, 1) = 'A' OR SUBSTR ('AB0123456', 1, 1) = 'B')                       ----Here is  MY first condition
AND SUBSTR ('AB0123456', 2, 1) =  
   SUBSTR (TRANSLATE('AB0123456',CHR (1)||TRANSLATE('AB0123456',CHR (1)
           || 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', CHR (1)), CHR (1)),2,1)       ---Here is My Second Condition 
AND SUBSTR ('AB0123456', 3, 7) = 
     (TRANSLATE ('AB0123456',CHR (1)||TRANSLATE ('AB0123456',CHR (1)||'1234567890',CHR(1)),CHR(1))) ---Here is My Third Condition



Thanking you again,


[Updated on: Fri, 15 February 2008 09:57]

Report message to a moderator

Previous Topic: Top-N query
Next Topic: HOW to retrieve values for 12 hours
Goto Forum:
  


Current Time: Sun Dec 04 10:27:01 CST 2016

Total time taken to generate the page: 0.17172 seconds