2

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jitesh
  • 23
  • 1
  • 3

1 Answers1

2

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
  • 422,506
  • 82
  • 585
  • 676
  • Yup. That worked. i had initially found charindex function which was available in SQL Server and not in DB2. LOCATE worked perfect – Jitesh Apr 07 '10 at 19:08