0

I have a table in db2 with these values and columns.

part    location    quantity
456     09B4        500
456     94C7        850
456     73B1        700
567     38A5        945
879     MR43F       1241
651     DC234       456
654     C34         546
456     B342        756
567     73B1        800

I need a sql statement for db2 that would be do this.

SELECT quantity FROM tablename WHERE part = '456' 
AND ((first two digits of location are numeric) OR (location LIKE 'MR%')

I'm not sure how to go about doing this in db2. I need all the rows where part number is '456' and the first two characters of location are numeric and also the rows that start with 'MR'

Angelo
  • 407
  • 8
  • 18
  • None of the rows you show meet that criteria. Is that what you intended? When you say `first two digits of location AND location like 'MR%'` it would seem you mean a location that starts with 'MR' and has two digits anywhere else in the location. – Charles Oct 27 '14 at 14:56

1 Answers1

1

I don't think DB2 has built-in support for regular expressions. But, you can do character by character comparisons:

SELECT quantity
FROM tablename
WHERE part = '456' AND
      ((left(location, 1) BETWEEN '0' and '9' AND
        substr(location, 2, 1) BETWEEN '0' and '9'
       ) OR
       location LIKE 'MR%'
      )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786