Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: Are the first 2 Characters a letter??

    I do not have any experience with DB2, but i have an issue that i need to resolve. I want to select all records from a table where the first 2 characters within a field are alphanumeric. I know that this can easily be achieved within SQL Server i.e.

    Select * from table Where field like ([A-z],[A-z]%)

    Can anyone provide me with an example of how to do this in DB2

    Thank you all in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code snippet that you posted won't work in either Microsoft SQL Server or DB2. I'd use:
    Code:
    SELECT *
       FROM MyTable
       WHERE  MyColumn LIKE '[A-Za-z][A-Za-z]%';
    Which ought to work in both of them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2013
    Posts
    8
    Pat, thank you for your prompt response, it is most appreciated. I have tried your code snippet and unfortunatly it does not work. When i run the SQL no records are returned. Have you any ideas??

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try this:
    Code:
    CREATE TABLE MyTable (
       id               INT
    ,  MyColumn         VARCHAR(9)
       );
    
    INSERT INTO MyTable (id, MyColumn)
       VALUES (1, 'A'), (2, 'AA'), (3, '3BA'), (4, 'b2b');
    
    SELECT *
       FROM MyTable
       WHERE  MyColumn LIKE '[A-Za-z][A-Za-z]%';
    
    DROP TABLE MyTable;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2013
    Posts
    8
    Pat,

    Thank you, i have tried the attached code, and though it does create a temp table with the values, the select statement still does not work??

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think Regular Expressions are supported with LIKE in DB2 for LUW - any version.

    Andy

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Quote Originally Posted by amporter View Post
    Pat,

    Thank you, i have tried the attached code, and though it does create a temp table with the values, the select statement still does not work??
    Plain DB2 for LUW (versions up to v10.5) does not support regular expressions - although additional features for searching are available in add on $$ products.

    For the example SQL given previously, this query may help, if you mean alpha-only and a length minimum of 2 characters:

    Code:
    select mycolumn from mytable where length(mycolumn) >= 2 and translate(substr(mycolumn,1,2),'','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz')='' order by 1

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ARWinner View Post
    I do not think Regular Expressions are supported with LIKE in DB2 for LUW - any version.
    Good point, I'd overlooked that limitation.

    If amporter is using LUW, then the SELECT statement would become;
    Code:
    SELECT *
       FROM MyTable
       WHERE  Upper(Substr(MyColumn, 1, 1)) BETWEEN 'A' AND 'Z'
          AND Upper(SubStr(MyColumn, 2, 1)) BETWEEN 'A' AND 'Z';
    As an alternative, you can probably use fn:matches() but I have not tested that on LUW

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by amporter View Post
    I want to select all records from a table where the first 2 characters within a field are alphanumeric.
    Hi,

    Since DB2 9.1:
    Code:
    select c
    from table(values 'aaa', 'Aa', 'a1a', ' a', '1aa') t(c)
    where xmlcast(xmlquery('fn:matches($s, "^[a-zA-z]{2}")' passing c as "s") as int)=1
    
    C  
    ---
    aaa
    Aa

  10. #10
    Join Date
    Jul 2013
    Posts
    8
    Pat / Mark,

    Thank you for the information, and i'm glad to say that both of the last suggested solutions worked. I have just found out the at the version of DB2 that I am using is 9.5, sorry, but it did not have this initially.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •