Search code examples
mysqlcoldfusionsql-like

Using LIKE operator to retrieve header codes only

I have a simple question. I have a list of head codes and related sub codes:

  • 100.001 (head code)
  • 100.001.001-100.001.010 (sub code)
  • 990.001 (head code)
  • 990.001.001-120.001.010 (sub code)

The head codes are all listed, and underneath them their sub codes. All I want is to list just the head codes. I tried many variations, but none of the worked.

I also want to get the codes from 760.001 to 790.001 and I achieved it easily like this:

WHERE ACCOUNT_CODE >= '760'
AND   ACCOUNT_CODE <= '790'

Then I wanted to get just head codes:

WHERE ACCOUNT_CODE LIKE '7%0.00%`

But it still lists all of the codes from 760.001.001 to 790.001.010. How do I filter them correctly?

Solution

  • You want to use the _ wildcard to match only one character, instead of the % wildcard which matches any number of characters - for example:

    ACCOUNT_CODE LIKE '7_0.00_'