Forum Stats

  • 3,874,142 Users
  • 2,266,672 Discussions
  • 7,911,738 Comments

Discussions

Present of a character

862489
862489 Member Posts: 135
edited Aug 22, 2012 3:54PM in SQL & PL/SQL
To check whether a character is present in the input string etc

for eg:14562d.

I know the position of the character d in the input, have to determine the presence of the character d if i know its position of it in the incoming input;
Pls suggest on this

Thanks
Tagged:

Answers

  • Biju Das
    Biju Das Member Posts: 393
    Use INSTR/REGEXP_INSTR

    Regards
    Biju
  • ShankarViji
    ShankarViji Member Posts: 498
    Hi,

    Query :
    SELECT LENGTH (trim (TRANSLATE ('12345d', '+-.0123456789', ' ')))
      FROM dual;
    The return value is NULL if no character is present. Returns numeric value representing the number of characters
    present in it.

    Thanks,
    Shankar
  • elessar
    elessar Member Posts: 64
    If you want to use regular expressions you could use the query below:-
    with temp as 
    (
      select '14562d' col from dual
      union
      select '14562'  from dual
      union
      select '14A562'  from dual
      union
      select 'A14A562'  from dual
      union
      select 'A14A562A'  from dual
       union
      select 'A14A562Aaaa'  from dual
    )
    select COL,REGEXP_INSTR(col,'[a-zA-Zfrom temp
    where REGEXP_LIKE(col,'[a-zA-Z]')
    IT WILL GIVE THE POSITION OF FIRST CHARACTER
  • KDaemonV
    KDaemonV Member Posts: 10
    edited Aug 22, 2012 2:28PM
    select decode(substr('14562d',:position_of_d,1),'d',1,0) from dual
    returns 1 if 'd' is on its position
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    select * from dual
    where
    instr ('14562d', 'd') = 6
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond
    edited Aug 22, 2012 3:11PM
    chris227 wrote:
    select * from dual
    where
    instr ('14562d', 'd') = 6
    Incorrect. Your query will return first occurence of 'd' in a string. OP needs to check if 'd' is in specific position.

    SY.
  • Purvesh K
    Purvesh K Member Posts: 2,319 Silver Trophy
    edited Aug 22, 2012 3:06PM
    select 'Character Exists' from table_name where substr(column_name,  :known_position_of_char, 1) != 0
    union
    select 'Character does not exist in Column' from dual;
    Edited by: Purvesh K on Aug 23, 2012 12:35 AM
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    Solomon Yakobson wrote:
    Incorrect. Your query will return first occurence of 'd' in a string. OP needs to check if 'd' is in specific position.
    Ah, sorry right, should be substr
    with data as (
     select '14562d' d from dual union all
     select 'd14562' from dual union all
     select 'd1456d' from dual union all
     select '14d562d' from dual 
    )
    
    select * from data
    where
    substr (d, 6, 1) = 'd'
    
    D
    14562d
    d1456d
    But even this might not be what OP is looking for ;-)
  • 759013
    759013 Member Posts: 39
    edited Aug 22, 2012 3:54PM
    You can use the following sql. Note I tried finding character 'A' located at first position. It A was located at first position it says exists other wise not exists.

    select ename,
    (
    CASE SUBSTR(ename,1,1)
    WHEN 'A' THEN 'EXISTS'
    ELSE 'NOT EXISTS'
    END
    ) AS STRING
    from emp;


    ENAME STRING_
    SMITH NOT EXISTS
    ALLEN EXISTS
    WARD NOT EXISTS
    JONES NOT EXISTS
    MARTIN NOT EXISTS
    BLAKE NOT EXISTS
    CLARK NOT EXISTS
    SCOTT NOT EXISTS
    KING NOT EXISTS
    TURNER NOT EXISTS
    ADAMS EXISTS
    JAMES NOT EXISTS
    FORD NOT EXISTS
    MILLER NOT EXISTS

    Edited by: Greenwich on Aug 22, 2012 3:53 PM
This discussion has been closed.