OraFAQ Forum: SQL & PL/SQL » string function or utility help

Home » SQL & PL/SQL » SQL & PL/SQL » string function or utility help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

string function or utility help [message #300268]

Thu, 14 February 2008 16:44

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.

Report message to a moderator

Re: string function or utility help [message #300269 is a reply to message #300268]

Thu, 14 February 2008 17:19

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]');

Report message to a moderator

Re: string function or utility help [message #300273 is a reply to message #300269]

Thu, 14 February 2008 19:36

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

Report message to a moderator

Re: string function or utility help [message #300296 is a reply to message #300269]

Thu, 14 February 2008 23:07

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: string function or utility help [message #300476 is a reply to message #300299]

Fri, 15 February 2008 09:57

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 15 01:53:22 CST 2025