tags:

views:

85

answers:

1

I have a table in db2 which has the following fields

  • int xyz;
  • string myId;
  • string myName;

Example dataset

xyz  |  myid         | myname
--------------------------------
1    |  ABC.123.456  | ABC
2    |  PRQS.12.34   | PQRS
3    |  ZZZ.3.2.2    | blah

I want to extract the rows where myName matches the character upto "." in the myId field. So from the above 3 rows, I want the firs 2 rows since myName is present in myId before "."

How can I do this in the query, can I do some kind of pattern matching inside the query?

+1  A: 

LEFT and LOCATE work in the DB2 instance I can connect to (which may not help of course!)

So hopefully something like this...

SELECT
    *
FROM
    MyTable Z
WHERE
    LEFT(myid, LOCATE('.', myid)) = myname + '.'
gbn
Yup. That worked. i had initially found charindex function which was available in SQL Server and not in DB2. LOCATE worked perfect
Jitesh
@Jitesh: please accept the answer (the big tick on the left hand side then)
gbn