CODEWITHSUNDEEP
sqldb2
Sewder
Sewder

Reputation: 754

Return values that have first three characters are letters

Is there a way to only return rows that have the first 3 values are alphabetic. Something like 

       Select GUITAR_DESC
       From Prod.INVENTOR
       WHERE LEFT(GUITAR_DESC,3)LIKE('[A-Z]%'

Should return

        FEN314
        GIB452

This doesn't work. Currently is there anyway?

Thanks!

Upvotes: 0

Views: 602

Answers (2)

bhamby
bhamby

Reputation: 15469

If you have a fairly new version of DB2 (tested on 10.1 for z/OS, and 9.7 Linux/Unix/Windows), you can use regex with Xquery:

SELECT *
FROM your_table A
WHERE
    XMLCAST(
        XMLQUERY('fn:matches($col, "^[A-Z]")' passing A.your_column AS "col")
    AS INTEGER) = 1

Upvotes: 1

krivtom
krivtom

Reputation: 24916

Many SQL engines support range of letters in LIKE clause, so you could be using it like this:

Select GUITAR_DESC
From Prod.INVENTOR
WHERE GUITAR_DESC LIKE '[A-Z][A-Z][A-Z]%'

Unfortunately it seems that DB2 does not support this option. As a workaround you can use substr function and BETWEEN operator like this:

Select GUITAR_DESC
From Prod.INVENTOR
WHERE (substr(GUITAR_DESC,1,1) BETWEEN 'A' AND 'Z') AND
      (substr(GUITAR_DESC,2,1) BETWEEN 'A' AND 'Z') AND
      (substr(GUITAR_DESC,3,1) BETWEEN 'A' AND 'Z')

Upvotes: 1

Related Questions